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
 

插入数据：(100万条)
SET IDENTITY_INSERT TestTable ON
declare @i int
set @i=1
while @i<=1000000
begin
    insert into TestTable([id], FirstName, LastName, Country,Note) values(@i, 'FirstName_XXX','LastName_XXX','Country_XXX','Note_XXX')
    set @i=@i+1
end
SET IDENTITY_INSERT TestTable OFF

------------------------------------

分页方案一：(利用Not In和SELECT TOP分页)

当前页数从0编号

(默认ASC排序)
//当前页数=0时用SQL：
SELECT TOP 每页记录数 *
FROM TestTable
ORDER BY ID

//当前页数>0时用SQL：
SELECT TOP 每页记录数 *
FROM TestTable
WHERE (ID NOT IN
          (SELECT TOP 每页记录数*当前页数 id    //当前页数必须>0
         FROM TestTable
         ORDER BY id))
ORDER BY ID
**************************
用DESC排序时，需要改成：
//当前页数=0时用SQL：
SELECT TOP 每页记录数 *
FROM TestTable
ORDER BY ID DESC

//当前页数>0时用SQL：
SELECT TOP 每页记录数 *
FROM TestTable
WHERE (ID NOT IN
          (SELECT TOP 每页记录数*当前页数 id    //当前页数必须>0
         FROM TestTable
         ORDER BY id DESC))
ORDER BY ID DESC
-------------------------------------

分页方案二：(利用ID大于多少和SELECT TOP分页）效率最高！严重推荐

当前页数从0编号

(默认ASC排序)
SELECT TOP 每页记录数 *
FROM TestTable
WHERE (ID >=
          (SELECT MAX(id)
         FROM (SELECT TOP (每页记录数*当前页数)+1 id   //当前页数从0编号
                 FROM TestTable
                 ORDER BY id) AS T))
ORDER BY ID
*************************
用DESC排序时，需要改成：
SELECT TOP 每页记录数 *
FROM TestTable
WHERE (ID <=
          (SELECT MIN(id)
         FROM (SELECT TOP (每页记录数*当前页数)+1 id   //当前页数从0编号
                 FROM TestTable
                 ORDER BY id DESC) AS T))
ORDER BY ID DESC

================================================

不管用哪种方法,设置条件都在最里面的select里

如:
把id>=100 且 FirstName='FirstName_XXX'的记录分页
(默认ASC排序)
SELECT TOP 每页记录数 * FROM TestTable
WHERE (ID >=
          (SELECT MAX(id)
         FROM (SELECT TOP (每页记录数*当前页数)+1 id   //当前页数从0编号
                 FROM TestTable
                 where id >=100 and FirstName='FirstName_XXX' //设置条件
                 ORDER BY id) AS T))
ORDER BY ID