SQL分页的几种写法

1.分页写法一：(利用Not In和SELECT TOP分页)

SELECT TOP 页大小 *
FROM TestTable
WHERE (ID NOT IN
          (SELECT TOP (页大小*(页数-1)) id
         FROM 表
         ORDER BY id))
ORDER BY ID

2.分页写法二：(利用ID大于多少和SELECT TOP分页）)

SELECT TOP 页大小 *
FROM TestTable
WHERE (ID >=
          (SELECT MAX(id)
         FROM (SELECT TOP (页大小*(页数-1)+1) id
                 FROM 表
                 ORDER BY id) AS T))
ORDER BY ID

3.分页写法三：（利用SQL的游标存储过程分页）

create  procedure SqlPager
@sqlstr nvarchar(4000), –查询字符串
@currentpage int, –第N页
@pagesize int –每页行数
as
set nocount on
declare @P1 int, –P1是游标的id
 @rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1, @rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pagesize) as 总页数–,@rowcount as 总行数,@currentpage as 当前页 
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize 
exec sp_cursorclose @P1
set nocount off

4. 分页写法四（利用rownumber分页）（推荐）

set statistics time on;
-- 分页查询（通用型）
select top pageSize * 
from (select row_number() 
over(order by sno asc) as rownumber,* 
from student) temp_row
where rownumber>((pageIndex-1)*pageSize);

5. 分页写法五（offset fetch next分页）（高版本的SQL推荐）

set statistics time on;
-- 分页查询（通用型）
select * from student
order by sno 
offset ((@pageIndex-1)*@pageSize) rows
fetch next @pageSize rows only;