SQL server分页

一. 三重循环

-- 设置执行时间开始，用来查看性能的
set statistics time on ;
-- 分页查询（通用型）
select *
from (select top pageSize *
from (select top (pageIndex*pageSize) *
from [user]
order by Id asc ) -- 其中里面这层，必须指定按照升序排序，省略的话，查询出的结果是错误的。
as temp_sum_user
order by userId desc ) temp_order
order by userId asc
 
set statistics time on ;
-- 分页查询第2页，每页有10条记录
select *
from (select top 10 *
from (select top 20 *
from [user]
order by userId asc ) -- 其中里面这层，必须指定按照升序排序，省略的话，查询出的结果是错误的。
as temp_sum_user
order by userId desc ) temp_order
order by userId asc;

二：max（主键）

set statistics time on;
-- 分页查询（通用型）
select top pageSize *
from [User]
where UserID>=
(select max(UserID)
from (select top ((pageIndex-1)*pageSize+1) UserID
from student
order by UserID asc) temp_max_ids)
order by UserID;
 
 set statistics time on;
-- 分页查询第2页，每页有10条记录
select top 10 *
from [User]
where UserID>=
(select max(UserID)
from (select top 11 UserID
from [User]
order by UserID asc) temp_max_ids)
order by UserID;

 三：row_number (server 2005版本以上才有)

set statistics time on;
-- 分页查询（通用型）
select top pageSize *
from (select row_number()
over(order by UserID asc) as rownumber,*
from [User]) temp_row
where rownumber>((pageIndex-1)*pageSize);
 
set statistics time on;
-- 分页查询第2页，每页有10条记录
select top 10 *
from (select row_number()
over(order by UserID asc) as rownumber,*
from [User]) temp_row
where rownumber>10;

 四：offset /fetch next（2012版本及以上才有）

set statistics time on;
-- 分页查询（通用型）
select * from [User]
order by UserID
offset ((@pageIndex-1)*@pageSize) rows
fetch next @pageSize rows only;
 
set statistics time on;
-- 分页查询第2页，每页有10条记录
select * from [User]
order by UserID
offset 10 rows
fetch next 10 rows only ;