首页 / 知识

分页SQL Server 2005结果

2023-04-11 17:08:00

Paging SQL Server 2005 Results

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

我在SQL Server 2000中进行了尝试，但是没有可靠的方法来执行此操作。我现在想知道SQL Server 2005是否有任何内置方法？

分页的意思是，例如，如果我按用户名列出用户，我希望只能返回前10条记录，然后返回前10条记录，依此类推。

任何帮助将非常感激。

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

1 2	SELECT ROW_NUMBER() OVER(ORDER BY UserName) AS RowID, UserFirstName, UserLastName FROM Users

从中将产生带有RowID字段的结果集，您可以使用该字段在页面之间进行分页。

1
2
3
4
5
6

SELECT *
FROM
( SELECT ROW_NUMBER() OVER(ORDER BY UserName) AS RowID, UserFirstName, UserLastName
FROM Users
) AS RowResults
WHERE RowID BETWEEN 5 AND 10

等等

如果您试图在一个语句中获得它(总数加分页)。您可能需要探索SQL Server对partition by子句的支持(用ANSI SQL术语显示窗口功能)。在Oracle中，语法类似于上面使用row_number()的示例，但是我还添加了partition by子句，以获取分页返回的每一行所包含的总行数(总行为1,262)：

1
2
3
4
5
6

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

请注意，我在所有者='CSEIS'的地方，分区依据是所有者。因此结果是：

1
2
3
4
5
6

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

对此的公认答案实际上对我不起作用...我不得不再跳一圈才能使它起作用。

当我尝试答案

1
2
3

SELECT ROW_NUMBER() OVER(ORDER BY UserName) AS RowID, UserFirstName, UserLastName
FROM Users
WHERE RowID BETWEEN 0 AND 9

它失败，抱怨它不知道什么是RowID。

我必须将其包装在内部选择中，如下所示：

1
2
3
4
5
6
7

SELECT *
FROM
(SELECT
ROW_NUMBER() OVER(ORDER BY UserName) AS RowID, UserFirstName, UserLastName
FROM Users
) innerSelect
WHERE RowID BETWEEN 0 AND 9

然后它起作用了。

当我需要分页时，通常也使用一个临时表。您可以使用输出参数返回记录总数。 select中的case语句使您可以对特定列上的数据进行排序，而无需求助于动态SQL。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

--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

这就是我要做的分页：我所有需要分页的大查询都被编码为临时表中的插入。临时表具有一个标识字段，该字段将以与上述row_number()类似的方式起作用。我将临时表中的行数存储在一个输出参数中，以便调用代码知道总共有多少条记录。调用代码还指定了它想要的页面，以及每页从临时表中选择的行数。

这样做的好处是，我还有一个"导出"链接，该链接可让您获取应用程序中每个网格上方以CSV格式返回的报告中的所有行。该链接使用相同的存储过程：您只返回临时表的内容，而不执行分页逻辑。这使那些讨厌分页，想看一切并想以一百万种不同方式对其进行排序的用户感到安心。

我相信您需要执行单独的查询才能无条件地完成此任务。

我可以使用此页面上的一些帮助在以前的位置完成此操作：
在DotNet 2.0中分页

他们还可以单独拉动行数。

分页执行操作用户名