一次往返MS-SQL Server 2005的查询分页，通过一个简单的查询

• 下载演示项目 - 19.8 KB

引言

如果您不在网站中使用 DataGrid 和所有其他预制的用户控件，那么您必须解决如何以最少的数据库服务器交互次数来创建分页查询。流行的 Web 数据库服务器（如 MySQL 或 Firebird）使用专有语法，例如 "LIMIT 20, 10" 或 "first 5 skip 2" 等。

您必须识别结果中有多少条记录，然后计算页数和所有为进行分页查询所需的项。这意味着两次往返数据库，当表很大、扩展的、巨大的且速度慢时，这不是很方便。在 MS-SQL Server 2005 公用表表达式 (CTE) 中，可以在一次往返数据库服务器中完成分页查询，并且您无需使用存储过程、视图或其他功能。

本文解释了如何仅使用 T-SQL 和 C# 来实现此功能。

例如：您在 AdventureWorks 数据库上有一个简单的 SQL 查询，例如

SELECT ProductID , [Name], ProductNumber, MakeFlag, FinishedGoodsFlag, 
   Color, SafetyStockLevel
FROM Production.Product 
WHERE ProductNumber like 'BK%' ORDER BY [Name] DESC

需要提取 "Order By" 子句并裁剪掉第一个单词 ("SELECT")。顺序字段是必需的！！！没有顺序字段，Row_Number 函数无法工作。如果您没有任何可用的字段，建议在 Order By 子句中使用主键。此查询稍长，但您可以将我声明的 T-SQL 变量替换为应用程序代码中的离散值，然后在构建此查询。原始查询是可见的。

declare @explicitMaximum int
declare @chosenPage int
declare @recordsPerPage int
set @explicitMaximum = 100
set @chosenPage = 2
set @recordsPerPage=10;

WITH X AS (
SELECT TOP(@explicitMaximum)
ROW_NUMBER() OVER (ORDER BY [Name] DESC) AS RowNumber, -- extracted order 
                                                       -- clause
CASE WHEN COUNT(*) OVER(PARTITION BY '') >@explicitMaximum THEN 
    @explicitMaximum ELSE COUNT(*) OVER(PARTITION BY '') END as totalCount,
------------ Original query
ProductID , [Name], ProductNumber, MakeFlag, FinishedGoodsFlag, Color, 
    SafetyStockLevel
FROM Production.Product 
WHERE ProductNumber like 'BK%' ORDER BY [Name] DESC
------------
) SELECT 
(totalCount-1)/@recordsPerPage+1 as actualPageCount, *
FROM x WHERE (RowNumber-1)/@recordsPerPage+1 = CASE WHEN (
   totalCount-1)/@recordsPerPage+1<@chosenPage THEN (
   totalCount-1)/@recordsPerPage+1 ELSE @chosenPage END

您必须接收三个新的冗余字段 {actualPageCount, RowNumber, totalCount}，但只有 RowNumber 实际上是无用的。当您想在 Web 上使用此结果时，所有其他字段都是必需的。如果分页结果只有 20-50 条记录，这不是很重要，您可以使用任何奇怪的名称来命名这些字段。此组合可以抵抗以下事件：@explicitMaximum 小于查询中的 count(*)，或 @chosenPage 高于 actualPageCount，但此事件也必须在 Web 应用程序中处理，并且必须影响最近选择的页面。

从查询中提取 order 字段的 C# 代码位于本文的附加项目中。Order 子句只能在查询中出现一次，并且只能在外部 Select 中（不在撇号内）。可以通过空格分割 SQL 查询，然后使用此数组来查找 Order 子句。