分页

 数据库   ZeroIsStart   2024-10-19 22:40   23
  从入门到精通SQL Server

SQL Server 分页查询详解：使用 ORDER BY 和 OFFSET-FETCH

分页查询是处理大量数据时常用的技术，它能够帮助我们一次获取指定数量的数据，并通过页码控制每次获取的不同数据集。在 SQL Server 中，可以通过 ORDER BY 和 OFFSET-FETCH 关键字实现分页查询。本文将详细介绍 SQL Server 分页的概念，使用 ORDER BY 和 OFFSET-FETCH 的方法，并通过丰富的示例展示分页查询的使用场景。

1. 什么是分页查询？

分页查询是指将数据集按页分割，每次只返回一页的数据。这对于查询大量数据时非常有用，能够避免一次返回所有数据，减轻数据库服务器的负担，也可以加快查询速度，尤其是展示在前端界面时，用户可以按需翻页查看数据。

2. ORDER BY 与 OFFSET-FETCH 关键字介绍

在 SQL Server 中，分页通常通过 ORDER BY 和 OFFSET-FETCH 实现：

  • ORDER BY：指定查询结果的排序规则。分页操作一般在已排序的数据上进行，这样才能确定哪一页的数据该先显示，哪一页后显示。
  • OFFSET：跳过指定数量的行，告诉 SQL Server 从结果集的哪一行开始返回数据。
  • FETCH：用于指定返回的行数。配合 OFFSET，可以控制每页返回的数据量。

3. 基本语法

SQL Server 中使用 OFFSET 和 FETCH 进行分页查询的基本语法如下：

SELECT 列名
FROM 表名
ORDER BY 列名
OFFSET 偏移行数 ROWS 
FETCH NEXT 返回行数 ROWS ONLY;
  • OFFSET 偏移行数：表示从结果集中的第几行开始读取（从 0 开始计算）。
  • FETCH NEXT 返回行数 ROWS ONLY：表示每次查询返回的行数。

4. 使用 OFFSET-FETCH 进行分页查询的示例

4.1 示例：获取第一页数据

假设我们有一个 Employees 表，现在要对员工信息进行分页查询。每页显示 10 条记录，按 EmployeeID 排序：

SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
ORDER BY EmployeeID
OFFSET 0 ROWS -- 从第一行开始
FETCH NEXT 10 ROWS ONLY; -- 获取接下来的 10 行

解释：

  • OFFSET 0 ROWS：从结果集的第一行开始（即没有跳过任何行）。
  • FETCH NEXT 10 ROWS ONLY：返回接下来的 10 行数据，即第一页的数据。

4.2 示例：获取第二页数据

为了获取第二页的数据，我们需要跳过第一页的 10 条记录：

SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
ORDER BY EmployeeID
OFFSET 10 ROWS -- 跳过前 10 行
FETCH NEXT 10 ROWS ONLY; -- 获取接下来的 10 行

解释：

  • OFFSET 10 ROWS：跳过第一页的 10 条记录。
  • FETCH NEXT 10 ROWS ONLY：获取接下来的 10 行记录，即第二页的数据。

4.3 示例：动态分页

如果我们想实现动态分页查询，可以通过参数来动态控制页码和每页显示的记录数：

DECLARE @PageNumber INT = 3; -- 页码
DECLARE @RowsPerPage INT = 10; -- 每页显示的记录数

SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
ORDER BY EmployeeID
OFFSET (@PageNumber - 1) * @RowsPerPage ROWS -- 跳过前 (页码 - 1) * 每页记录数 的行
FETCH NEXT @RowsPerPage ROWS ONLY; -- 获取接下来的 @RowsPerPage 行

解释：

  • @PageNumber：当前页码（例如第 3 页）。
  • @RowsPerPage：每页显示的记录数。
  • OFFSET (@PageNumber - 1) * @RowsPerPage ROWS：根据页码动态跳过前几页的数据。例如，第 3 页会跳过前 20 行。
  • FETCH NEXT @RowsPerPage ROWS ONLY：返回当前页的数据。

5. 分页查询中的 ORDER BY 子句

分页查询必须依赖 ORDER BY 子句，原因是分页操作需要在排序后的数据集上进行。否则，分页可能导致结果的不确定性，因为数据库在无序的数据集上分页时，返回的行可能不一致。

5.1 按照多个列排序

我们可以使用多个列进行排序，以确保结果集的顺序是我们期望的。例如，按照 DepartmentID 和 Salary 进行排序：

SELECT EmployeeID, FirstName, LastName, Salary, DepartmentID
FROM Employees
ORDER BY DepartmentID, Salary DESC
OFFSET 20 ROWS -- 跳过前 20 行
FETCH NEXT 10 ROWS ONLY; -- 获取接下来的 10 行

解释：

  • ORDER BY DepartmentID, Salary DESC：首先按 DepartmentID 排序，然后在部门内按 Salary 降序排列。

6. OFFSET-FETCH 的注意事项

6.1 必须有 ORDER BY 子句

在使用 OFFSET-FETCH 时，ORDER BY 子句是必需的。没有排序的分页是没有意义的，因为我们无法确保数据是按照一定顺序返回的。

6.2 OFFSET 不等于 ROW_NUMBER()

尽管 OFFSET-FETCH 能实现分页，但它与 ROW_NUMBER() 函数生成的行号不同。ROW_NUMBER() 是基于排序生成的行号，而 OFFSET-FETCH 仅跳过行，二者并不直接相关。

7. 使用 ROW_NUMBER() 实现分页

在某些情况下，我们可能需要更加灵活的分页方法，例如对已经排序的结果生成唯一的行号。这时，可以结合 ROW_NUMBER() 函数实现分页。

7.1 示例：使用 ROW_NUMBER() 分页

WITH EmployeeRank AS (
    SELECT EmployeeID, FirstName, LastName, Salary,
           ROW_NUMBER() OVER (ORDER BY EmployeeID) AS RowNum
    FROM Employees
)
SELECT EmployeeID, FirstName, LastName, Salary
FROM EmployeeRank
WHERE RowNum BETWEEN 11 AND 20; -- 获取第 2 页的数据

解释：

  • ROW_NUMBER() OVER (ORDER BY EmployeeID)：为结果集中的每一行生成一个唯一的行号，按照 EmployeeID 排序。
  • WHERE RowNum BETWEEN 11 AND 20：只获取行号在 11 到 20 之间的数据，即第二页的数据。

8. OFFSET-FETCH 与 TOP 的比较

在 SQL Server 中，TOP 关键字也可以用于限制返回的数据行数，但 TOP 无法实现分页功能，因为它只能返回前 N 行数据。OFFSET-FETCH 可以实现分页，而 TOP 更适合简单的前 N 行查询。

8.1 示例：使用 TOP 限制行数

SELECT TOP 10 EmployeeID, FirstName, LastName, Salary
FROM Employees
ORDER BY Salary DESC;

这条查询会返回工资最高的前 10 名员工。

8.2 示例：使用 OFFSET-FETCH 进行分页

相比之下，OFFSET-FETCH 能够跳过前几行并返回接下来的数据，这使得它更适合分页查询：

SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
ORDER BY Salary DESC
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

这条查询会返回工资排名第 11 到 20 名的员工。

9. 实战应用：分页查询与搜索功能结合

在实际应用中，分页查询常用于与搜索功能结合。假设我们有一个用户搜索页面，用户可以输入关键字搜索员工姓名，并通过分页显示搜索结果。下面是一个结合搜索条件与分页功能的查询示例：

DECLARE @SearchTerm NVARCHAR(50) = 'John';
DECLARE @PageNumber INT = 1;
DECLARE @RowsPerPage INT = 10;

SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE FirstName LIKE '%' + @SearchTerm + '%' OR LastName LIKE '%' + @SearchTerm + '%'
ORDER BY EmployeeID
OFFSET (@PageNumber - 1) * @RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY;

解释：

  • 用户输入的搜索关键词 @SearchTerm 用于匹配 FirstName 或 LastName，只返回匹配的数据。
  • 通过分页功能控制每页显示的结果，避免一次返回过多数据。

10. 总结

通过本文，你已经学习了如何使用 SQL Server 中的 ORDER BY 和 OFFSET-FETCH 实现分页查询

，了解了分页查询的基本原理及其在实际项目中的应用场景。无论是静态分页还是动态分页，SQL Server 提供了灵活高效的工具，使得分页操作简单且高效。

分页查询是现代 Web 应用程序中不可或缺的一部分，掌握 OFFSET-FETCH 能够帮助我们优化数据库查询，提升用户体验。