sql分页(1)利用Not In和SELECT TOP分页
SQL分页技术有许多方法,根据数据量的不同采用不同的方法,根据项目以及个人掌握知识的程度选择不同的方法,下面讲解利用Not In和SELECT TOP分页,
1.创建表
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
2.插入数据(20000条)
SET IDENTITY_INSERT TestTable ON
declare @i int
set @i=1
while @i<=20000
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
3.利用Not In和SELECT TOP分页
SELECT TOP 10 *
FROM TestTable
WHERE (ID NOT IN
(SELECT TOP 20 id
FROM TestTable
ORDER BY id))
ORDER BY ID
4.sql语句公式
SELECT TOP 页大小 *
FROM TestTable
WHERE (ID NOT IN
(SELECT TOP 页大小*页数 id
FROM 表
ORDER BY id))
ORDER BY ID
5.思路
规定:每页条数10条,
查看第2页
1.查出前20条的数据的id,并排序
SELECT TOP 20 id FROM TestTable ORDER BY id
2.将以上的结果作为where条件,查询整表的数据但是不包括以上数据的前10条(每页条数)数据,并排序