SQL Server分页查询|极客笔记

SQL Server分页查询

1. 简介

分页查询是在查询结果集中按页获取数据的操作，常用于显示长列表或分页加载数据。在SQL Server中，我们可以使用OFFSET...FETCH和ROW_NUMBER()函数来实现分页查询。

2. OFFSET…FETCH语法

OFFSET...FETCH语句结合ORDER BY子句使用，用于指定从查询结果中跳过多少行并获取多少行数据。其基本语法如下：

SELECT 列名
FROM 表名
ORDER BY 排序列
OFFSET 行数 ROWS    -- 跳过指定行数
FETCH NEXT 行数 ROWS ONLY;   -- 获取指定行数的数据

例如，下面的查询语句将跳过前10行数据并获取接下来的20行数据：

SELECT *
FROM Employees
ORDER BY EmployeeID
OFFSET 10 ROWS
FETCH NEXT 20 ROWS ONLY;

3. ROW_NUMBER()函数

ROW_NUMBER()函数是一种用于为每行数据生成唯一的行号的函数，可以用它来实现通用的分页查询。ROW_NUMBER()函数的语法如下：

ROW_NUMBER() OVER (ORDER BY 排序列) AS 行号

例如，下面的查询将为每行数据生成一个递增的行号：

SELECT ROW_NUMBER() OVER (ORDER BY EmployeeID) AS RowNum, *
FROM Employees;

运行结果如下：

RowNum  EmployeeID  FirstName  LastName   Age
1       1           John       Doe        30
2       2           Jane       Smith      25
3       3           Mark       Johnson    35
...

4. 结合OFFSET…FETCH和ROW_NUMBER()进行分页查询

我们可以结合OFFSET...FETCH和ROW_NUMBER()函数来实现分页查询。具体步骤如下：
1. 使用ROW_NUMBER()函数为每行数据生成行号。
2. 使用OFFSET...FETCH语句跳过指定行数并获取指定行数的数据。

下面是一个示例，假设我们有一个名为Products的表，其中包含了大量商品数据。我们希望每页显示10条数据，获取第3页的数据：

SELECT *
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY ProductID) AS RowNum, *
    FROM Products
) AS Result
WHERE Result.RowNum > 20     -- 第3页的起始行号为21
    AND Result.RowNum <= 30  -- 第3页的结束行号为30
ORDER BY Result.RowNum;

运行结果如下：

RowNum  ProductId  ProductName     Category
21      21         Product 21      Category 1
22      22         Product 22      Category 2
23      23         Product 23      Category 3
...
30      30         Product 30      Category 10

5. 动态分页查询

以上的示例中，我们是根据页码手动计算出需要跳过的行数，这种方式不够灵活。在实际应用中，我们通常会根据用户的输入动态生成分页查询语句。我们可以使用变量来存储页码和每页显示的行数，然后在查询中使用这些变量。

下面是一个示例，假设用户希望显示第5页的数据，每页显示15条：

DECLARE @PageNumber INT = 5;
DECLARE @PageSize INT = 15;

SELECT *
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY ProductID) AS RowNum, *
    FROM Products
) AS Result
WHERE Result.RowNum > (@PageNumber - 1) * @PageSize
    AND Result.RowNum <= @PageNumber * @PageSize
ORDER BY Result.RowNum;

运行结果如下：

RowNum  ProductId  ProductName     Category
61      61         Product 61      Category 1
62      62         Product 62      Category 2
63      63         Product 63      Category 3
...
75      75         Product 75      Category 10