Sql server分页的总结

可能很多人在开始学sql分页时候都是在使用TOP NOT IN的方式。可能到现在还有很多人在用这种分页的方式进行。但是有人发现问题了么？

SELECT TOP 10 * FROM dbo.Orders WHERE OrderID NOT IN (SELECT TOP 20 OrderID FROM dbo.Orders ORDER BY RequiredDate) ORDER BY dbo.Orders.RequiredDate

SELECT TOP 10 * FROM dbo.Orders WHERE OrderID NOT IN (SELECT TOP 30 OrderID FROM dbo.Orders ORDER BY RequiredDate) ORDER BY dbo.Orders.RequiredDate

　　 OrderID编号为 10788两条语句有重复

　　这是我用NORTHWND 测试的TOP方式的分页，大家也可以测试下，这两条语句查询出来的结果会有重复。测试如果orderby 的列如果有重复的话那么这样分页出来的数据就会有重复存在。不管是倒序还是正序都会出现。如果有人有兴趣的话可以试试下面这条。

SELECT TOP 10 * FROM dbo.Orders WHERE OrderID NOT IN (SELECT TOP 160 OrderID FROM dbo.Orders ORDER BY RequiredDate DESC) ORDER BY dbo.Orders.RequiredDate DESC

SELECT TOP 10 * FROM dbo.Orders WHERE OrderID NOT IN (SELECT TOP 170 OrderID FROM dbo.Orders ORDER BY RequiredDate DESC) ORDER BY dbo.Orders.RequiredDate DESC

　　OrderID 编号为10880两条语句有重复

　　很多人都怀疑是语句哪里写错了或这数据有问题。我用的是微软官方的northwnd库，应该不会存在数据的问题。那么我语句有问题么？

　　以上是初学或者习惯使用了，那么我们来实现另一种在 sqlserver2000里的实现。很多人都说都换 sqlserver2005了干嘛还拿sqlserver2000来说事。我觉得只要有人在用那么问题就会一直存在就要提出解决。

　　那么我们继续看sqlserver2000的临一种写法，表变量来做。我想很多人也都在使用这样的方法。

DECLARE @PAGETEMP TABLE
(

__ROW_NUM INT IDENTITY(1,1),

__TID INT

)

INSERT INTO @PAGETEMP(__TID) SELECT TOP 30 OrderID FROM dbo.Orders  order by RequiredDate

SELECT [@PAGETEMP].__ROW_NUM,* FROM Orders,@PAGETEMP WHERE dbo.Orders.OrderID= [@PAGETEMP].__TID AND [@PAGETEMP] .__ROW_NUM>20 AND [@PAGETEMP].__ROW_NUM<=30

首先建立一个带自增字段的表变量 (__ROW_NUM) ，然后把对应分页表的主键插入该临时表 (__TID),这个临时表就把查询表的主键字段根据查询条件进行了重新的自增排序。那么下一步就是根据这个重新排序好的表变量进行与查询表关联。得到想返回的行数。表变量分页和05的分页方式很像。都是根据查询内容得到一个带有自增序号的临时表，然后得到需要的行数。

　　继续看另一种Sqlserver2000分页，这样的方法也可以解决上面的重复问题。但是给排序带来了局限性。

SELECT TOP 10 * FROM [Orders] WHERE [Orders].[OrderID]>(
SELECT MAX([__T].[OrderID]) FROM
(SELECT TOP 20 [Orders].[OrderID] AS [OrderID] FROM [Orders] ORDER BY [Orders].[OrderID]) [__T])
ORDER BY [Orders].[OrderID]

　　首先找到前20条数据找到最大的那一条编号。排除这20条记录找到大于这写记录的前十条记录。很容易看明白。这个必须是查询表有自增编号。而且按这个编号排序进行分页，有了很大的局限性。那如果倒序的话就是min和小于来过滤数据。

SELECT TOP 10 * FROM [Orders] WHERE [Orders].[OrderID]<(
SELECT MIN([__T].[OrderID]) FROM
(SELECT TOP 20 [Orders].[OrderID] AS [OrderID] FROM [Orders] ORDER BY [Orders].[OrderID] DESC) [__T])
ORDER BY [Orders].[OrderID] DESC

　　最后我们来看一下sqlserver2005的分页语句,用到 ROW_NUMBER OVER 两个关键字

WITH [__T] AS (
SELECT TOP 30 *,ROW_NUMBER() OVER  (ORDER BY [Orders].OrderID) AS [__Pos] FROM [Orders]
)
SELECT * FROM [__T] WHERE [__T].[__Pos]>20 AND [__T].[__Pos]<=30

　　OVER 这里设置排序列。ROW_NUMBER() OVER (ORDER BY [Orders].OrderID) AS [__Pos]为我们建立了自增列，查询的时候只需要返回我们需要的行号就行。这个和表变量的形式很相像。性能方面也提高了很多。

　　以上就是我对分页作出的总结，主要是想说下sqlserver2000的TOP NOT IN 这种分页方式，所以以后大家要用Sqlserver2000的话请使用后面2种方式。在新的NSun中默认是sqlserver2005的分页，如果你是sqlserver2000则变为了表变量的方式。