SQL SERVER 的分页语句

最近在工作中遇到了对Sql Server数据分页查询的问题，即是如果浏览一个产品网页时，往往在一个页面显示一些产品，但产品很多，就要通过分页去显示这些产品。现在来说说我想到的这几种方法：
首先定义几个“参数”，假设要查询的表是{TableName}，Where条件为{Conditions}，排序字段是{OrderId}。还有两个整形变量：一个页面显示的产品数：[PageSize]，当前是第几页：[CurrentPage]，假设都已赋确定的值，且[[PageSize] * ([CurrentPage] - 1)] 和 [[PageSize] * [CurrentPage]] 也是一个确定的值。

方法一：
这种方法是我在网上找到的，这要分两种情况：
1。查询的顺序是"ASC"

1SELECT TOP [PageSize] * FROM {TableName} WHERE {Conditions} AND {OrderId} >
2    (SELECT MAX({OrderId}) FROM
3        (SELECT TOP [[PageSize] * ([CurrentPage] - 1)]  {OrderId} FROM {TableName}
4        WHERE {Conditions} ORDER BY {OrderId} ASC))
5ORDER BY {OrderId} ASC

2。查询的顺序是"DESC"

1SELECT TOP [PageSize] * FROM {TableName} WHERE {Conditions} AND {OrderId} <
2    (SELECT MIN({OrderId}) FROM
3        (SELECT TOP [[PageSize] * ([CurrentPage] - 1)]  {OrderId} FROM {TableName}
4        WHERE {Conditions} ORDER BY {OrderId} DESC))
5ORDER BY {OrderId} DESC

不过这种方法有一个缺点，就是{OrderId}字段必须唯一，如果{OrderId}有重复值的话查询的结果就会出错，于是我想了自己的方法。

方法二：
这种方法用到“集合差”原理，至于“集合差”原理在这里就不详解了。

 1SELECT * FROM
 2    ((SELECT * FROM
 3        (SELECT DISTINCT TOP [[PageSize] * ([CurrentPage] - 1)] {Field1}, {Field2}, {Field3}, , {FieldN}
 4        FROM {TableName} WHERE {Conditions} ORDER BY {OrderId} ASC/DESC) Table1)
 5    UNION ALL
 6    (SELECT * FROM
 7        (SELECT DISTINCT TOP [[PageSize] * [CurrentPage]] {Field1}, {Field2}, {Field3}, , {FieldN}
 8        FROM {TableName} WHERE {Conditions} ORDER BY {OrderId} ASC/DESC) Table2) {TableName}
 9GROUP BY {Field1}, {Field2}, {Field3}, , {FieldN} HAVING COUNT(*) = 1

这种方法就可以避免了{OrderId}字段值的重复了，但检索出来的{TableName}表的数据不能有重复，要用DISTINCT关键字避免重复，而且必须像{Field1}, {Field2}, {Field3}, ..., {FieldN} 这样列举出所有要查询的列，不能用“*”号列出所有列。
于是我想到了第三种方法。

方法三：

1SELECT * FROM
2    (SELECT TOP [PageSize] * FROM
3        (SELECT TOP [[PageSize] * [CurrentPage]] * FROM {Table}
4        WHERE {Conditions} ORDER BY {OrderId} ASC/DESC) Table1
5    ORDER BY {OrderId} DESC/ASC) {Table}
6ORDER BY {OrderId} ASC/DESC

这种方法可以避免了{OrderId}字段值的重复和{TableName}表数据的重复，也可以用“*”号列出所有列，但这种方法在最后一页的时候会选择[PageSize]行数据，而不是剩余行的数据。但SQL SERVER 2005可以允许在 TOP 语句中使用表达式，于是方法三在SQL SERVER 2005中可得到改良，语句如下：

 1SELECT * FROM (SELECT TOP
 2        (CASE
 3            WHEN
                    ((SELECT COUNT(*) FROM {TableName}) - [[PageSize] * ([CurrentPage] - 1)]) > [PageSize]
 4            THEN [PageSize]
 5            ELSE (SELECT COUNT(*) FROM {TableName})  - [[PageSize] * ([CurrentPage] - 1)]
 6        END)
 7    * FROM
 8        (SELECT TOP [[PageSize] * [CurrentPage]] * FROM {TableName}
 9        WHERE {Conditions} ORDER BY {OrderId} ASC/DESC) Table1 
10    ORDER BY {OrderId} DESC/ASC) {TableName}
11ORDER BY {OrderId} ASC/DESC

其中

1CASE
2    WHEN
            ((SELECT COUNT(*) FROM {TableName}) - [[PageSize] * ([CurrentPage] - 1)]) > [PageSize]
3        THEN [PageSize]
4    ELSE (SELECT COUNT(*) FROM {TableName})  - [[PageSize] * ([CurrentPage] - 1)]
5END

用来判断是否是最后一页，这样就可以解决了以上的问题。但由于SQL SERVER 2000的 TOP 语句不可以使用表达式，所以不可以用于SQL SERVER 2000。然而在SQL SERVER 2005中这种方法也已是多余的了，因为SQL SERVER 2005有更好的方法。

方法四：
SQL SERVER 2005增加了一个 Over 语句和 ROW_NUMBER() 函数，用法如下：

1SELECT * FROM
2    (SELECT *, ROW_NUMBER() OVER({OrderId} ASC/DESC) AS RowNum
3    FROM {TableName} WHERE {Conditions} ) {TableName}
4WHERE RowNum BETWEEN [[PageSize] * [CurrentPage] - [PageSize] + 1]
        AND [[PageSize] * [CurrentPage]]