sqlserver 分页查询

SQLServer 的数据分页:

假设现在有这样的一张表：
CREATE TABLE test
(
 id int primary key not null identity,
 names varchar(20)
)
然后向里面插入大约1000条数据，进行分页测试
假设页数是10，现在要拿出第5页的内容，查询语句如下：
--10代表分页的大小
select top 10 *
from test
where id not in
(
 --40是这么计算出来的：10*(5-1)
 select top 40 id from test order by id
)
order by id

原理：需要拿出数据库的第5页，就是40-50条记录。首先拿出数据库中的前40条记录的id值，然后再拿出剩余部分的前10条元素

第二种方法：
还是以上面的结果为例，采用另外的一种方法
--数据的意思和上面提及的一样
select top 10 *
from test
where id >
(
 select isnull(max(id),0)
 from 
  (
   select top 40 id from test order by id
  ) A
)
order by id
原理：先查询前40条记录，然后获得其最id值，如果id值为null的，那么就返回0
然后查询id值大于前40条记录的最大id值的记录。
这个查询有一个条件，就是id必须是int类型的。

第三种方法：
select top 10 *
from 
(
 select row_number() over(order by id) as rownumber,* from test
) A
where rownumber > 40
原理：先把表中的所有数据都按照一个rowNumber进行排序，然后查询rownuber大于40的前十条记录
这种方法和oracle中的一种分页方式类似，不过只支持2005版本以上的

第四种：
存储过程查询
创建存储过程
alter procedure pageDemo
@pageSize int,
@page int
AS
declare @temp int
set @temp=@pageSize*(@page - 1)
begin
 select top (select @pageSize) * from test where id not in (select top (select @temp) id from test) order by id
end
执行存储过程

exec 10,5

Sql Server分页分段查询百万级数据四种项目实例

实际项目中需要实现自定义分页，最关键第一步就是写分页SQL语句，要求语句效率要高。

那么本文的一个查询示例是查询第100000-100050条记录，即每页50条的结果集。查询的表名为infoTab，且id为整型自增主键字段。

 

第一种、使用NOT IN关键字

SELECT TOP 50 * FROM infoTab WHERE ID NOT IN(SELECT TOP 100000 ID FROM infoTab)

平均查询时间0.1s。这是非常简单的一种写法，效率还可以，只是使用默认ID排序，如果ID有间断，展示效果不好。

 

第二种、通过升序与降序方式实现分页查询

SELECT * FROM (SELECT TOP 50 * FROM (SELECT TOP 100050 * FROM infoTab ORDER BY ID ASC)TEMP1 ORDER BY ID DESC)TEMP2 ORDER BY ID ASC

平均查询时间0.22s。这种是利用先查询倒序的50个数据，最后又进行升序排序的方法很复杂，不推荐使用，也最浪费时间，效率最低。

 

第三种、采用MAX(ID)函数

实际是关于MIN()函数和MAX()函数的使用

SELECT TOP 50 * FROM infTab WHERE ID>(SELECT MAX(ID) FROM (SELECT TOP 100000 ID FROM infoTab ORDER BY ID)temp) 

平均查询时间0.13s。这种方法理解起来很简单，就是直接使用MAX()进行查找，然后id大于MAX()结果为100000的前50条记录即为结果。

 

第四种、最后一种推荐使用的方法，使用ROW_NUMBER

正如Oracle的rowid，解决了整型的ID字段不连续的问题。用行号（ROW_NUMBER）查询，比较高效的查询方式，只有在SQL Server2005或更高版本才支持。

注意的是，ROW_NUMBER后面必须跟着OVER(ORDER BY [FIELD])，我们来看具体的写法。

SELECT * FROM (SELECT TOP 100050 ROW_NUMBER() OVER(ORDER BY ID ASC) AS rowid,* FROM infoTab)t WHERE t.rowid > 100000

平均运行时间0.29s。这种方法的好处是实现连续的排序ROWID,避免了ID间断不连续的问题，展示更加美观。

　　在这里面需要注意的是OVER的括号里面可以写多个排序字段，比如：OVER(ORDER BY CreatedTime, ID)。

如果使用下面这条语句更高效，是上面语句运行时间的三分之一，平均运行时间仅0.08s。

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID ASC) AS rowid,* FROM infoTab)t WHERE t.rowid > 100000 AND t.rowid <= 100050

 

总之，我们推荐使用第四种方法中的后面一条语句，查询效率高，而且展示结果美观，ROWID连续，尤其在千万级数据查询的时候，效率相差很大非常大。

（所有SQL语句实测通过，可直接在项目中使用。）