SQL Server实现分页方法介绍_MsSql_

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