T-SQL查询数据的分页

建立表：
CREATE TABLE [TestTable] (
 [ID] [int] IDENTITY (1, 1) NOT NULL ,
 [FirstName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [LastName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [Country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [Note] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

插入数据：(1万条)
SET IDENTITY_INSERT TestTable ON
declare @i int
set @i=1
while @i<=10000
begin
    insert into TestTable([id], FirstName, LastName, Country,Note) values(@i, 'FirstName_'+convert(varchar(5),@i),'LastName_'+convert(varchar(5),@i),'Country_'+convert(varchar(5),@i),'Note_'+convert(varchar(5),@i))
    set @i=@i+1
end
SET IDENTITY_INSERT TestTable OFF

第一种

select top 每页记录数 * from TestTable where ID not in (select top 排除的数据 id from TestTable)
--排除的数据=(要查的页数-1)*每页记录数

第二种

declare @selectpage varchar(max)
declare @page int --每页记录数
declare @mycount int --(每页记录数*当前页数)+1
set @page=50
set @mycount=(@page*5)+1
select @page as 每页记录数,@mycount 计算
set @selectpage='select top '+convert(varchar(50),@page)+' * from TestTable where (Id>=(select MAX(id) from (select Top '+convert(varchar(50),@mycount)+' ID from TestTable order by id) as t)) order by id'
--select top 10 * from TestTable where (Id>=(select MAX(id) from (select Top 21 ID from TestTable order by id) as t)) order by id
exec(@selectpage)

转载于:https://www.cnblogs.com/wangchao2011/archive/2011/05/01/wangchao_00002.html