从SQL Server端分页记录集

Question

我已经把头撞飞了好一阵子了。我想从SQL数据库中调用1k条记录，并每100页对它们进行分页。在传统的ASP中(我正在迁移的地方)，用ADODB很容易做到这一点，但在使用ADO.net的VB中，我找不到一种不涉及存储过程的方法(这是我现在想避免的)。调用所有1k并以编程方式对其进行排序似乎真的很愚蠢。

编辑: SQL Server2005/ .net 4.0 / Visual Studio2010。

编辑2:重申一下，我已经广泛地搜索过了，不想使用存储过程。有许多方法可以获取分页数据，但我看到的所有方法都涉及到在程序中分页数据，而不是从服务器中分页数据。

编辑3:这是一个使用经典ASP的服务器端分页的例子，它不需要任何服务器端的东西。(它使用Access，但对SQL也是如此) https://support.microsoft.com/kb/202125

Answer 1

在这里，ROW_NUMBER()是您的答案。如果您的数据集具有支持deterministic结果的字段，则可以绑定到上述字段，如下所示：

构建测试数据集：

DECLARE @RecordSet TABLE
(
    ID  INT IDENTITY(1,1),
    VALUE   VARCHAR(255)
)

INSERT INTO @RecordSet (VALUE) VALUES ('One')
INSERT INTO @RecordSet (VALUE) VALUES ('Two')
INSERT INTO @RecordSet (VALUE) VALUES ('Three')
INSERT INTO @RecordSet (VALUE) VALUES ('Four')
INSERT INTO @RecordSet (VALUE) VALUES ('Five')
INSERT INTO @RecordSet (VALUE) VALUES ('Six')
INSERT INTO @RecordSet (VALUE) VALUES ('Seven')
INSERT INTO @RecordSet (VALUE) VALUES ('Eight')
INSERT INTO @RecordSet (VALUE) VALUES ('Nine')
INSERT INTO @RecordSet (VALUE) VALUES ('Ten')

确定性运行基于ID或VALUE字段：

-- Deterministic - Ordering Numerically by PKey
SELECT ID, VALUE, RowNum
FROM
(
    SELECT ID, VALUE, ROW_NUMBER() OVER (PARTITION BY 0 ORDER BY ID) AS RowNum
    FROM @RecordSet
    WHERE VALUE LIKE '%e%'
) t
WHERE  t.RowNum BETWEEN 3 AND 6

-- Deterministic - Ordering Alphanumerically by Value
SELECT ID, VALUE, RowNum
FROM
(
    SELECT ID, VALUE, ROW_NUMBER() OVER (PARTITION BY 0 ORDER BY VALUE) AS RowNum
    FROM @RecordSet
    WHERE VALUE LIKE '%e%'
) t
WHERE  t.RowNum BETWEEN 3 AND 6

正如您所看到的，您可以根据RowNum字段调整WHERE子句来“窗口”您的结果。

如果您遇到无法确定地生成结果的情况(例如nondeterministic)，但仍然需要对事物进行窗口操作，则可以使用以下技巧，我将其归功于这篇CONNECT文章：http://connect.microsoft.com/SQLServer/feedback/details/383888/fully-support-position-in-xquery

-- Nondeterministic Ordering -- Probably want to avoid as these results are not guaranteed to be repeatable
SELECT ID, VALUE, RowNum
FROM
(
    SELECT ID, VALUE, ROW_NUMBER() OVER (PARTITION BY 0 ORDER BY NULLIF(0*RAND(), 0)) AS RowNum
    FROM @RecordSet
    WHERE VALUE LIKE '%e%'
) t
WHERE  t.RowNum BETWEEN 3 AND 6

希望这能有所帮助，

约翰