可能很多人在开始学 sql 分页时候都是在使用TOP NOT IN 的方式。可能到现在还有很多人在用这种分页的方式进行。但是有人发现问题了么？ SELECT TOP 10 * FROM dbo.Orders WHERE OrderID NOT IN ( SELECT TOP 20 OrderID FROM dbo.Orders ORDER BY RequiredDa
可能很多人在开始学 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 则变为了表变量的方式。