在 SQL Server 2005 中进行分页查询结果

Question

如何在SQL Server 2005中分页查询结果？

我曾尝试在SQL Server 2000中实现，但没有可靠的方法。我现在想知道SQL Server 2005是否有内置的方法？

所谓的分页，例如，如果我按用户名列出用户，我希望能够仅返回前10条记录，然后是下一组10条记录，依此类推。

非常感谢任何帮助。

- GateKiller

Answer 1

您可以使用 Row_Number() 函数。其用法如下：

SELECT Row_Number() OVER(ORDER BY UserName) As RowID, UserFirstName, UserLastName
FROM Users

通过该方法，您将获得一个结果集，其中包含一个 RowID 字段，您可以使用它进行翻页。

SELECT * 
FROM 
    ( SELECT Row_Number() OVER(ORDER BY UserName) As RowID, UserFirstName, UserLastName
      FROM Users 
    ) As RowResults
WHERE RowID Between 5 AND 10

- Pat

---

非常好的、简单的例子，Pat——完全符合我的要求 :) - Town

这个答案对我没有用，虽然它让我更接近解决。它抱怨不知道什么是RowID。如果你有同样的问题，请看下面的我的答案获取额外信息。 - Beska

在内部查询中，您可以选择前X行（其中X = 所需的最大行数，在本例中为10）。这将提高查询速度。 - Faruz

正如上面的评论者所提到的，通过在内部表中添加“TOP”来提高此代码的性能：SELECT TOP 10 Row_Number() OVER...。您只会获取所需的行，而不是整个表。 - Doug S

Answer 2

如果您想在一个语句中获取总数和分页信息，您可能需要探索SQL Server对于partition by子句(ANSI SQL术语中的窗口函数)的支持。在Oracle中，语法与上面的示例一样，使用row_number()，但我也添加了一个partition by子句来获取每个返回的行中包含的总行数（总行数为1,262）：

SELECT rn, total_rows, x.OWNER, x.object_name, x.object_type
FROM (SELECT COUNT (*) OVER (PARTITION BY owner) AS TOTAL_ROWS,
         ROW_NUMBER () OVER (ORDER BY 1) AS rn, uo.*
         FROM all_objects uo
         WHERE owner = 'CSEIS') x
WHERE rn BETWEEN 6 AND 10

请注意，我的查询条件为 owner = 'CSEIS'，并且我的分区是按照 owner 进行的。因此，结果如下：

Note that I have where owner = 'CSEIS' and my partition by is on owner. So the results are:

RN  TOTAL_ROWS  OWNER   OBJECT_NAME            OBJECT_TYPE
6   1262    CSEIS   CG$BDS_MODIFICATION_TYPES   TRIGGER
7   1262    CSEIS   CG$AUS_MODIFICATION_TYPES   TRIGGER
8   1262    CSEIS   CG$BDR_MODIFICATION_TYPES   TRIGGER
9   1262    CSEIS   CG$ADS_MODIFICATION_TYPES   TRIGGER
10  1262    CSEIS   CG$BIS_LANGUAGES            TRIGGER

- Brian

---

+1 我在想如何在不使用临时表的情况下获取总行数和页数。谢谢！ - dotjoe

+1 很好 - 使用 COUNT(*) OVER (PARTITION BY NULL) 成功在 SQL Server 上运行 - Hafthor

+1...不错。现在可以玩一下了 :-) 你知道使用COUNT(*) OVER(...)会有任何性能影响吗？ - Chris J

一般来说，任何直接在SQL中完成的操作都是最佳选择。换句话说，任何你可以用 '单个' SQL 查询完成的操作很可能会优于更加编程化的方法，无论是Java/pl/SQL C#。尽可能经常阅读Kyte先生的博客：http://tkyte.blogspot.com/2006/10/slow-by-slow.html。 - Brian

Answer 3

对于这个问题的已接受答案对我并不起作用...我不得不再多做一步才能让它工作。

当我尝试了这个答案时

SELECT Row_Number() OVER(ORDER BY UserName) As RowID, UserFirstName, UserLastName
FROM Users
WHERE RowID Between 0 AND 9

出现错误，提示不知道RowID是什么。

我不得不像这样将其包装在内部选择中：

SELECT * 
FROM
    (SELECT
    Row_Number() OVER(ORDER BY UserName) As RowID, UserFirstName, UserLastName
    FROM Users
    ) innerSelect
WHERE RowID Between 0 AND 9

然后它就正常工作了。

- Beska

Answer 4

当我需要进行分页时，通常也会使用临时表。您可以使用输出参数返回记录的总数。选择语句中的case语句允许您按特定列对数据进行排序，而无需使用动态SQL。

--Declaration--

--Variables
@StartIndex INT,
@PageSize INT,
@SortColumn VARCHAR(50),
@SortDirection CHAR(3),
@Results INT OUTPUT

--Statements--
SELECT @Results = COUNT(ID) FROM Customers
WHERE FirstName LIKE '%a%'

SET @StartIndex = @StartIndex - 1 --Either do this here or in code, but be consistent
CREATE TABLE #Page(ROW INT IDENTITY(1,1) NOT NULL, id INT, sorting_1 SQL_VARIANT, sorting_2 SQL_VARIANT)
INSERT INTO #Page(ID, sorting_1, sorting_2)
SELECT TOP (@StartIndex + @PageSize)
    ID,
    CASE
        WHEN @SortColumn='FirstName' AND @SortDirection='ASC' THEN CAST(FirstName AS SQL_VARIANT)
        WHEN @SortColumn='LastName' AND @SortDirection='ASC' THEN CAST(LastName AS SQL_VARIANT)
        ELSE NULL
    END AS sort_1,
    CASE
        WHEN @SortColumn='FirstName' AND @SortDirection='DES' THEN CAST(FirstName AS SQL_VARIANT)
        WHEN @SortColumn='LastName' AND @SortDirection='DES' THEN CAST(LastName AS SQL_VARIANT)
        ELSE NULL
    END AS sort_2
FROM (
    SELECT
        CustomerId AS ID,
        FirstName,
        LastName
    FROM Customers
    WHERE
        FirstName LIKE '%a%'
) C
ORDER BY sort_1 ASC, sort_2 DESC, ID ASC;

SELECT
    ID,
    Customers.FirstName,
    Customers.LastName
FROM #Page
INNER JOIN Customers ON
    ID = Customers.CustomerId
WHERE ROW > @StartIndex AND ROW <= (@StartIndex + @PageSize)
ORDER BY ROW ASC

DROP TABLE #Page

- Andrew Burgess

---

在 SQL Server 2000 中，您会这样做，但是在2005版本中，使用 ROW_NUMBER 函数有更好的解决方案。 - niaher

Answer 5

我相信你需要执行一个单独的查询来完成这个，不幸的是。

我曾经在我的上一份工作中使用这个页面的帮助来完成这个目标：DotNet 2.0中的分页

他们还将它单独提取行数。

- Pat

Answer 6

这是我用于分页的方法：所有需要进行分页的大型查询都编码为插入到临时表中。临时表具有一个标识字段，其作用类似于上面提到的row_number()函数。我将临时表中的行数存储在输出参数中，以便调用代码知道总记录数。调用代码还指定了它想要的页面以及每个页面选择的行数，这些行数从临时表中选择出来。

这种方法的好处是，我还有一个“导出”链接，允许您从应用程序中的每个网格上方返回报告的所有行作为CSV。此链接使用相同的存储过程：只需返回临时表的内容而不是执行分页逻辑即可。这可以安抚那些讨厌分页、想看到所有内容并希望以无数种方式对其进行排序的用户。

- Eric Z Beard