SQL基础：SQL Server实现分页方法介绍

CREATE TABLE [dbo].[Student](
[id] [int] NOT NULL,
[name] [nvarchar](50) NULL,
[age] [int] NULL)

declare @i int
set @i=1
while(@i<10000)
begin
insert into Student select @i,left(newid(),7),@i+12
set @i += 1
end

declare @pageCount int –每页条数
declare @pageNo int –页码
declare @startIndex int –跳过的条数
set @pageCount=10
set @pageNo=3
set @startIndex=(@pageCount*(@pageNo-1))
select top(@pageCount) * from Student
where ID not in
(
select top (@startIndex) ID from Student order by id
) order by ID

declare @pageCount int –页数
declare @pageNo int –页码
set @pageCount=10
set @pageNo=3
–写法1：使用between and
select t.row,* from
(
select ROW_NUMBER() over(order by ID asc) as row,* from Student
) t where t.row between (@pageNo-1)*@pageCount+1 and @pageCount*@pageNo
–写法2：使用 “>”运算符
select top (@pageCount) * from
(
select ROW_NUMBER() over(order by ID asc) as row,* from Student
) t where t.row >(@pageNo-1)*@pageCount
–写法3：使用and运算符
select top (@pageCount) * from
(
select ROW_NUMBER() over(order by ID asc) as row,* from Student
) t where t.row >(@pageNo-1)*@pageCount and t.row<(@pageNo)*@pageCount+1