数据库分页是老生常谈的问题了。如果使用ORM框架，再使用LINQ的话，一个Skip和Take就可以搞定。但是有时由于限制，需要使用存储过程来实现。在SQLServer中使用存储过程实现分页的已经有很多方法了。之前在面试中遇到过这一问题，问如何高效实现数据库分页。刚好上周在业务中也遇到了这个需求，所以在这里简单记录和分享一下。
一 需求
这里以SQLServer的示例数据库NorthWind为例，里面有一张Product表，现在假设我们的需求是要以UnitPrice降序排列，并且分页，每一页10条记录。要求服务端分页。参数为每页记录数和页码。
二 实现
Top分页
当时采用的最直接做法就是使用两个Top来实现， 最后返回的结果是升序的，在C#代码里再处理一下就可以了。 这里作为演示，语句中使用 * 为了方便，实际开发中要替换为具体的列名。下面的方法简单吧。
SELECT TOP (@pageSize) * FROM ( SELECT TOP ( @pageSize * @pageIndex ) * FROM [Northwind].[dbo].[Products] ORDER BY UnitPrice DESC ) AS product ORDER BY product.UnitPrice |
但是这个代码是有问题的，不知道各位发现了没有。当符合条件的纪录集小于每页记录数时，没有问题，但是当大于就有问题了，比如，在实例数据库中Products中有 77 条记录，当每页10条记录，第8页只应该返回7条记录，第9页应该返回空，但是使用如上的方法，每次都会返回10条记录。
沿用上面的思路，把代码修改为了如下采用三层Select，最内一层查询所有记录之前的数据，然后第二层选择Top PageSize个所有NOT IN 第一层数据中的数据即可，因为使用了NOT IN所以不存在第一种方法中的bug
SELECT * FROM dbo.Products WHERE ProductID IN ( SELECT TOP ( @pageSize ) ProductID FROM dbo.Products WHERE ProductID NOT IN ( SELECT TOP ( @pageSize * (@pageIndex-1) ) ProductID FROM dbo.Products ORDER BY UnitPrice DESC ) ORDER BY dbo.Products.UnitPrice DESC ) ORDER BY dbo.Products.UnitPrice ASC |
使用ROW_NUMBER 函数分页
其实还有一种最简单最直接的思路，那就是采用临时表，即在内存中创建一个表变量，该变量中包含一个自增列，表关键字列，然后将待排序的表按照排序条件和规则插入到这张表中，然后就可以将自增列作为行号使用了，在比较早的如SQLServer 2000中，只能这样做，但是对于大数据量的记录集，需要创建的临时表也比较大，效率比较低，这里就不介绍了。
在SQLServer2005中引入了ROW_NUMBER() 函数,通过这个函数，可以根据给定好的排序字段规则，生成记录序号，其基本用法为：
SELECT ROW_NUMBER() OVER ( ORDER BY dbo.Products.ProductID DESC ) AS rownum ,
*
FROM dbo.Products
