SQL利用ROW_NUMBER()进行分页

<XmlCommand Name="fy_MobileExpenseMng_GetMobileExpenses">
<Parameters>
<Parameter Name="@StartNum" Type="Int32" />
<Parameter Name="@EndNum" Type="Int32" />
</Parameters>
<CommandText>
<![CDATA[
WITH LIST AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY A.CreatedTime DESC) AS Num,
A.*
FROM
(
SELECT fy_DailyExpense.CreatedTime ,
fy_DailyExpense.DailyExpenseGUID AS ExpenseGUID ,
fy_DailyExpense.ApproveMode,
fy_DailyExpense.ApproveState ,
fy_DailyExpense.ApproveStateEnum ,
fy_DailyExpense.ExpenseTemplateGUID AS ExpenseTemplateGUID ,
fy_DailyExpense.ApplyDate ,
fy_DailyExpense.ExpenseReason ,
fy_DailyExpense.ExpenseAmount
FROM dbo.fy_DailyExpense
WHERE BUGUID = @BUGUID
AND AppliedBy = @AppliedBy
AND ApproveStateEnum = @ApproveStateEnum
UNION
SELECT fy_BusTripExpense.CreatedTime ,
fy_BusTripExpense.BusTripExpenseGUID AS ExpenseGUID ,
ISNULL(fy_BusTripExpense.ApproveMode, 1) + '' AS ApproveMode ,
fy_BusTripExpense.ApproveState ,
fy_BusTripExpense.ApproveStateEnum ,
fy_BusTripExpense.ExpenseTemplateGUID AS ExpenseTemplateGUID ,
fy_BusTripExpense.ApplyDate ,
fy_BusTripExpense.ExpenseReason ,
fy_BusTripExpense.ExpenseAmount
FROM dbo.fy_BusTripExpense
WHERE BUGUID = @BUGUID
AND AppliedBy = @AppliedBy
AND ApproveStateEnum = @ApproveStateEnum
) AS A
WHERE 1=1 {QueryConditionText}
)
SELECT *
FROM LIST AS LT
WHERE LT.Num BETWEEN @StartNum AND @EndNum
]]>
</CommandText>
</XmlCommand>

 

 

 

第二种：

select
*
from (
select ROW_NUMBER() OVER (ORDER BY x_ApplicationDate DESC ) AS num,appr.* from
(
select distinct x_HonestAuditMatterApprovalGUID
from x_cb_HonestAuditMatterApprovaldetailed det
where det.x_HonestAuditMatterGUID in (
select distinct matter.HonestAuditMatterGUID from x_cb_HonestAuditMatter matter
left join x_v_CompanyToProject proj on proj.p_projectId = matter.x_ProjGUID
left join p_Project pp on pp.p_projectId = proj.p_projectId
where UserGUID =@UserGUID and (proj.BUGUID = @SourceGuid or proj.p_projectId = @SourceGuid or pp.ParentGUID = @SourceGuid)
)
) as tb inner join x_cb_HonestAuditMatterApproval appr on tb.x_HonestAuditMatterApprovalGUID = appr.HonestAuditMatterApprovalGUID
) as tba
where num BETWEEN @BeginNum AND @EndNum