65.9K
CodeProject 正在变化。 阅读更多。
Home

SQL Server 2005, SQL Server 2000, MS Access 和 MySQL 中的分页记录

Erwin@Attentia
starIconstarIconstarIcon
emptyStarIcon
starIcon
emptyStarIcon

3.89/5 (13投票s)

2007 年 3 月 3 日

CPOL

7分钟阅读

viewsIcon

87039

从各种数据库中获取有限的记录集。

引言

作为一名 Web 开发人员，您现在应该知道，使用 ASP.NET Web 控件（如 DataGrid 和 GridView）的默认分页功能是不推荐的，原因很简单，因为每次往返数据服务器，您都会始终获取所有记录。这对于非常小的数据库（例如，微软程序员用来开发新功能的数据库）来说可能还可以，但在现实世界的复杂环境中，数据库通常包含数万条记录的表。

因此，Web 控件需要自定义分页。

自定义分页各种数据库

自定义分页带来两个问题

  1. 如何从数据库获取一页记录（我使用“一页记录”或“分页记录”来表示一个有限的记录集，仅包含 ASP.NET Web 控件需要显示的记录）。但是，获取这些记录取决于数据服务器，因此您需要了解各种方法。这就是本文要讲的内容。
  2. 第二个问题是如何在 Web 控件中仅显示有限的记录集，但仍能让它自动显示一页行。对于 DataGrid（ASP.NET 1.x），这相当容易，但对于 GridView（ASP.NET 2.0），情况就大不相同了，特别是当绑定不是在设计时（声明性地）进行，而是在运行时（编程方式）进行时。我在网上没有找到任何有用的信息来解决这个问题，所以我想出了一个自己的方法。您可以在我之前写的文章（Custom Paging and the GridView ASP.NET 2.0）中找到它。

但是，就第一个问题（从数据库获取分页记录）而言，网上有很多解决方案。本文的大部分内容将仅指向这些解决方案并提供一些示例（使用 Northwind 示例数据库）。

让我们从 MySQL 提供的最简单的解决方案开始。

MySQL

MySQL 在 SELECT 语句中提供了一个 LIMIT 子句。

示例

SELECT * FROM Products LIMIT 0, 10

第一个参数表示从找到的第一条记录开始的偏移量，第二个参数表示要返回的记录数。因此，前面的示例将记录集限制为 Products 表中的前 10 条记录。

要查找下一批，您可以使用“SELECT * FROM Products LIMIT 10, 10”，然后是 LIMIT 20, 10 等等。

（请参阅 https://dev.mysqlserver.cn/doc/refman/5.0/en/select.html。）

LIMIT 子句在 MySQL 4 和 5 版本中可用，但我不知道之前的版本是否支持它。

SQL Server 2005

长期以来，微软一直需要类似 LIMIT 的功能，并在 SQL Server 2005 中提供了一个新的函数 ROWNUMBER()（与关键字 OVER 结合使用），可用于检索有限的记录集。

我将不详细介绍这个解决方案，原因有二：

  • 它不像 MySQL 中的 LIMIT 子句那样简单（微软真的应该致力于开发类似的东西！）。
  • 它仅适用于 SQL Server 2005，因此不适用于早期版本。

但是，如果您想在 ASP.NET 2.0 中以声明方式使用 GridView 和 ObjectDataSourceControl，我强烈建议您了解这种方法。为了帮助您，这里有两个链接：

SQL Server 2005, SQL Server 2000, SQL Server 7 和 MS Access

如果您使用上述数据库服务器进行分页记录，有几种解决方案。但其中许多都涉及使用临时表或游标，这当然会严重影响性能。

但是，有一个解决方案仅包含一个 SQL 语句，因此即使对于大型数据库也高效且快速。好消息是：它总是有效！但另一方面，它相当复杂。

首先，我给您找到它的链接：http://josephlindsay.com/archives/2005/05/27/paging-results-in-ms-sql-server/（原始条目尤其有趣，回复 #39 也很有价值）。

这是您应该使用的 SQL 语句的模板：

SELECT t.fields FROM (
     SELECT TOP x id_field, sort_field FROM (
          SELECT TOP y id_field, sort_field        
          FROM table
          WHERE conditions
          ORDER BY sort_field ASC, id_field ASC) AS foo
     ORDER BY sort_field DESC, id_field DESC) AS bar
INNER JOIN table AS t ON bar.id_field = t.id_field
ORDER BY bar.sort_field ASC, bar.id_field ASC

图例

SQL Server 关键字：大写单词不应更改

  • t = 表/视图的别名
  • fields = 您想在 Web 控件中显示的字段名称。它们应包括表/视图的主键字段（无论是否显示）。
  • x = 每页行数（您希望返回的行数）。
  • id_field = 表/视图的主键字段。
  • sort_field = 您想对其排序的字段（可选）。但请注意，排序字段始终包括主键字段！这是为了避免歧义结果。
  • y = 所需的最大记录数。这需要一些计算：y = 页码 * 每页记录数。所以，如果您想显示第三页，每页有 10 条记录，则 y = 3 x 10 = 30。（但是，SQL 语句只会返回 10 条记录，但 y 必须是所需的最大记录数。）
  • table = 要从中获取记录集的表/视图的名称。
  • conditions = 通常的 WHERE 子句放在这里。
  • foo = 只是一个名称，您可以使用它或选择另一个。
  • bar = 只是一个名称，同上。

示例

这将从 Products 表中返回前 10 条 UnitsInStock 小于 200 的产品，并按 ProductName 排序。要在 GridView 中显示的字段是 ProductID, ProductName, UnitPrice 和 UnitsInStock。

SELECT p.ProductID, p.ProductName, p.UnitPrice, p.UnitsInStock FROM
     (SELECT TOP 10 ProductID, ProductName FROM 
          (SELECT TOP 10 ProductID, ProductName
          FROM Products
          WHERE UnitsInStock < 200
          ORDER BY ProductName ASC, ProductID ASC) AS foo
     ORDER BY ProductName DESC, ProductID DESC) as bar
     INNER JOIN Products p ON bar.ProductID = p.ProductID
ORDER BY bar.ProductName ASC, bar.ProductID ASC

以下将返回第二页。请注意，只有 'y' 值发生了变化！

SELECT p.ProductID, p.ProductName, p.UnitPrice, p.UnitsInStock FROM
     (SELECT TOP 10 ProductID, ProductName FROM 
          (SELECT TOP 20 ProductID, ProductName
          FROM Products
          WHERE UnitsInStock < 200
          ORDER BY ProductName ASC, ProductID ASC) AS foo
     ORDER BY ProductName DESC, ProductID DESC) as bar
     INNER JOIN Products p ON bar.ProductID = p.ProductID
ORDER BY bar.ProductName ASC, bar.ProductID ASC

这是如何工作的？

您应该从内向外阅读 SQL 语句。

1. 最内层的 SELECT 语句
  1. 最内层的 SELECT 语句仅返回相关记录的主键字段（+ 可选的排序字段）（因此，WHERE 子句放在最内层的 SELECT 语句中）。
  2. 这些记录按升序排序（如果您想按降序排序，请参阅说明 2）。
  3. 同时，行数被限制为所需的最大行数。假设您的页面包含 10 行，第一页所需的记录数 = 10，第二页 = 20，第三页 = 30，依此类推。这意味着，如果您有一个包含 10,000 条记录的表，并且您想要最后 10 条记录，最内层的 SELECT 语句实际上将检索 10,000 条记录。这当然会对性能产生影响，但由于这只是主键字段（已索引）和可选的一些排序字段，因此影响会很小。此外，WHERE 子句将限制基础记录集的数量。
  4. 此记录集命名为“foo”。
2. 中间的 SELECT 语句
  1. 从“foo”记录集中，中间的 SELECT 语句仅选择要显示的记录。要显示的行数由 TOP x 子句设置（其中 x = 要显示的行数）。由于反向排序顺序，它将是最后 'x' 条记录。
  2. 这将为您提供一个记录集，命名为“bar”。
  3. 此记录集也仅包含两种类型的字段：主键字段和排序字段。
3. 外层的 SELECT 语句
  1. 在外层的 SELECT 语句中，'bar' 的主键字段与源表/视图的主键字段（使用 INNER JOIN 子句）进行短路连接，这样就可以检索其他字段（如 UnitPrice 和 UnitsInStock）。同样，此记录集必须按升序排序。

结论

SQL 语句通过排序顺序来限制记录，从而产生分页记录集。

这意味着至少应有一个要排序的字段。如果没有，则按主键字段排序！

备注

  1. 排序时，将主键字段放在排序字段之后。
  2. 如果您想按降序获得结果，那么您必须将所有 ASC 关键字更改为 DESC，将所有 DESC 更改为 ASC，这样就可以了！
  3. 当您到达表末尾时会遇到一个问题。假设您的表中共有 84 条记录，您想要最后一页，只应返回四条记录。与 MySQL 中的 LIMIT 语句不同，您必须自己进行计算。这意味着您需要跟踪是否请求了最后一页，如果是，则需要计算返回的记录数。这应该是 SQL 语句中 x 的值。
© . All rights reserved.