sql server 分页

使用公用表进行分页

declare @page int=1000000 -- 页码
declare @pageSize int=30 -- 页数
;with data_cte as
(
    select ROW_NUMBER () over(order by id desc)'rn',*
    from orders(nolock) 
)
select (select COUNT(0) from data_cte)'Count',*
from data_cte aa
where aa.rn between (@page-1)*@pageSize+1 and @page*@pageSize
order by rn

注意：排序列rn需要具有决胜属性，即ROW_NUMBER里的order by需要有确定的排列顺序