SQL Server 2005 分页结果

引言

在 SQL Server 2005 中，使用分页查询比以前的版本容易得多。 我将使用 NorthWind 数据库（主要），所以您也可以使用我提供的示例。 我将保持示例简单；任何复杂的事情只会导致混乱。 我将从“传统”方法开始，例如 SELECT、TOP，然后转向特定的 SQL Server 2005 分页示例。

背景

有人问了我一个或一系列问题，“你如何在 SQL 中进行分页？ 如何处理大量记录，例如 10,000 条或更多？”

我思考了答案。 更准确地说，我想到了更多的问题，这让我想到，“这一定是一个常见的问题，每个开发人员都必须做过或解决过这个问题。 分页大小和处理非常大的数据集呢？ 从多个表中获取结果呢？”

因此，我决定针对 SQL Server 2005 研究这些问题。 以下是迄今为止最简单的方法，应该使用，但很少有这么容易。

select * from mytable
Where ID between 20 and 30

SQL Top

SQL Top（从结果集的顶部返回记录）非常擅长从结果集的每一端返回一定数量的记录。 下面的示例按订单数量获取前 10 名客户。 这是论坛上非常常见的问题。 TOP 也可以提取一定百分比的记录，但这里没有讨论。

select top 10 * from customers -- This is a very basic example.
select TOP 10 Customers.CustomerID, Customers.CompanyName, count(*) OrderCount
from Customers inner join Orders on Orders.CustomerID = Customers.CustomerID
GROUP BY Customers.CustomerID, Customers.CompanyName
ORDER BY OrderCount DESC

这真的很有用。 当您想提取第 11 到 20 条记录时，您可以使用临时表。

-- SELECT First 30 records in to Temp table
SELECT TOP 30 * INTO
#TEMP
from Customers
ORDER BY CompanyName ASC

--Select Bottom 10 records in another temp table

SELECT TOP 10 * 
INTO #TEMP2
from #Temp
ORDER BY CompanyName DESC

-- GET THE RECORDS

SELECT * FROM #TEMP2

这对前几页或前几个用户来说很好。 如果您有用户希望一页又一页地返回页面，那么您最终会得到 1000 条记录来返回 10 条，这不是很有效。 您也可以在第一个临时表上放置一个标识，并使用 SELECT 语句作为替代方法。

TOP 的替代方案

TOP 有一个替代方案，那就是使用 rowcount。 小心使用 rowcount，因为它如果不关闭，会导致各种问题。

SET rowcount 10
SELECT * from Customers
ORDER BY CompanyName

WITH、ROW_NUMBER 和 OVER

这是 SQL Server 2005 的新增功能，看起来非常有用。 下面是一个从结果集中获取第 20 到 29 条记录的示例。 乍一看可能有点奇怪，但我将逐步讲解查询，以便您了解它有多么简单。

With Cust AS
    ( SELECT CustomerID, CompanyName,
    ROW_NUMBER() OVER (order by CompanyName) as RowNumber 
    FROM Customers )
select *
from Cust
Where RowNumber Between 20 and 30

SQL Server 2005 中的 WITH 指定了一个临时的命名结果，很像以前版本的 SQL Server 中的临时表。 但是，重要的部分是 ROW_NUMBER 和 OVER 语句，它们根据公司名称在每一行上创建一个行号。 这就像向带有 order by 子句的临时表添加标识种子。

我希望你还在跟着我。 如果没有，请运行代码并查看结果。 这对于大型表来说真的非常快； 表格超过 250,000 条记录的速度给我留下了深刻的印象。

将所有内容放在存储过程中

现在我们将所有内容放在您的应用程序可以使用的存储过程中。 我不会展示 .NET datagrid 或类似的控件，因为这超出了本文的范围。 下面的存储过程使用灵活的页面大小和页码，因此您可以随机选择任何页面。 如果您希望向前跳转 10 页以查找特定记录，这将非常有用。 此示例的分页从第 1 页开始，而不是从第 0 页开始，但这可以很容易地更改。

CREATE PROC GetCustomersByPage

@PageSize int, @PageNumber int 

AS 

Declare @RowStart int 
Declare @RowEnd int 

if @PageNumber > 0 
Begin 

SET @PageNumber = @PageNumber -1 

SET @RowStart = @PageSize * @PageNumber + 1; 
SET @RowEnd = @RowStart + @PageSize - 1 ; 

With Cust AS 
     ( SELECT CustomerID, CompanyName, 
       ROW_NUMBER() OVER (order by CompanyName) as RowNumber 
       FROM Customers ) 

select * 
from Cust 
Where RowNumber >= @RowStart and RowNumber <= @RowEnd end

END

要运行此程序，只需指定页面大小和页码（GetCustomersByPage、@PageSize 和 @PageNumber），如下所示。

exec GetCustomersByPage 10, 1

历史

• 版本 1.0 22/03/2007：初始版本
• 版本 1.1 18/04/2007：修复了在第一页之后获取 11 条记录的 proc 问题