SQL SERVER 各种分页方法

SQL Server 分页写法与说明

[TOC]

## 1. OFFSET ... FETCH 分页

`OFFSET ... FETCH` 是 SQL Server 2012 及以上版本引入的标准分页语法，也被称为“偏移量分页”，其核心语法 `OFFSET 0 ROWS FETCH NEXT 15 ROWS ONLY` 是实现分页的关键。下面从原理、逻辑、应用场景到具体示例进行详细说明。

### 1.1. 分页原理与逻辑

`OFFSET ... FETCH` 的工作原理是**先对结果集排序，再跳过指定数量的行，最后获取后续的指定行数**，具体拆解如下：

1. **`OFFSET N ROWS`**：表示“跳过前面 N 行数据”
   - `OFFSET 0 ROWS` 即跳过 0 行（从第 1 行开始）
   - `OFFSET 15 ROWS` 即跳过前 15 行（从第 16 行开始）

2. **`FETCH NEXT M ROWS ONLY`**：表示“只获取接下来的 M 行数据”
   - `FETCH NEXT 15 ROWS ONLY` 即只取 15 行数据

3. **依赖 `ORDER BY`**：
   必须配合 `ORDER BY` 使用，否则结果集顺序不确定，分页会出现数据重复或遗漏。排序字段通常是主键、时间戳或业务关键字（如价格、编号）。

**逻辑流程**：`排序结果集 → 跳过前 N 行 → 取接下来的 M 行`
**公式**：`第 X 页数据 = OFFSET (X-1)*M ROWS FETCH NEXT M ROWS ONLY`（M 为每页行数）

### 1.2. 应用场景与适用条件

`OFFSET ... FETCH` 适用于以下场景：

1. **Web/APP 列表分页**
   如电商商品列表、租房信息列表、新闻列表等，用户通过“上一页/下一页”或页码切换查看数据。

2. **数据量中等且分页深度不大的场景**
   - 适合页数较少的情况（如前 100 页），此时性能表现良好。
   - 不适合超深分页（如第 10000 页），因为 `OFFSET` 会扫描大量前置数据，效率下降。

3. **需要兼容 SQL 标准的场景**
   该语法符合 SQL:2008 标准，除 SQL Server 外，PostgreSQL、Oracle 12c+ 等也支持类似语法，便于跨数据库迁移。

4. **简单分页需求**
   无需复杂逻辑（如“跳至最后一页”），仅需按顺序获取数据时，比 `TOP` 分页更简洁。

### 1.3. 使用案例示例

假设我们有一个租房信息表 `rentHouse_info_tbl`，结构如下：

| 字段名       | 说明         |
|--------------|--------------|
| id           | 主键（自增） |
| title        | 房源标题     |
| price        | 租金（元/月）|
| area         | 面积（㎡）   |
| publish_time | 发布时间     |

下面通过示例说明如何用 `OFFSET ... FETCH` 实现分页：

#### 1.3.1. 获取第 1 页数据（1-15 条）

需求：按发布时间倒序（最新在前），获取前 15 条租房信息。
```sql
SELECT *
FROM operation_xm.dbo.rentHouse_info_tbl
ORDER BY publish_time DESC  -- 按发布时间降序（最新的先展示）
OFFSET 0 ROWS               -- 跳过 0 行（从第 1 行开始）
FETCH NEXT 15 ROWS ONLY;    -- 只取 15 行
```

#### 1.3.2. 获取第 2 页数据（16-30 条）

需求：延续第 1 页的排序，获取下一页 15 条数据。
```sql
SELECT *
FROM operation_xm.dbo.rentHouse_info_tbl
ORDER BY publish_time DESC
OFFSET 15 ROWS              -- 跳过前 15 行（第 1 页的数据）
FETCH NEXT 15 ROWS ONLY;    -- 取接下来的 15 行（第 16-30 条）
```

#### 1.3.3. 动态分页（结合变量）

在实际开发中，页数和每页行数通常是变量（如用户选择“每页 20 条”），可通过变量动态控制：
```sql
DECLARE @PageSize INT = 20;   -- 每页行数
DECLARE @PageNum INT = 3;     -- 目标页码（第 3 页）

SELECT *
FROM operation_xm.dbo.rentHouse_info_tbl
ORDER BY price ASC            -- 按租金升序（便宜的先展示）
OFFSET (@PageNum - 1) * @PageSize ROWS  -- 跳过 (3-1)*20=40 行
FETCH NEXT @PageSize ROWS ONLY;         -- 取 20 行（第 41-60 条）
```

#### 1.3.4. 与筛选条件结合
分页时通常会伴随查询条件（如筛选“面积大于 50㎡ 的房源”）：
```sql
SELECT *
FROM operation_xm.dbo.rentHouse_info_tbl
WHERE area > 50  -- 只看面积 50㎡ 以上的房源
ORDER BY price ASC
OFFSET 30 ROWS   -- 跳过前 30 行（前 2 页，每页 15 条）
FETCH NEXT 15 ROWS ONLY;  -- 第 3 页数据
```

### 1.4. 优缺点与替代方案

#### 1.4.1. 优点：

1. 语法简洁直观，比 `TOP` 分页更易理解和维护。
2. 符合 SQL 标准，兼容性较好（支持的数据库较多）。
3. 适合实现“上一页/下一页”的线性分页需求。

#### 1.4.2. 缺点：

1. 超深分页效率低：`OFFSET N` 会扫描前 N 行数据，当 N 很大（如 10 万）时，性能明显下降。
2. 数据实时性问题：若分页过程中数据有新增/删除，可能导致前后页数据重复或遗漏。

#### 1.4.3. 替代方案（针对超深分页）：
用“基于游标”的分页（如按主键定位）：
```sql
-- 假设上一页最后一条数据的 id 是 100，获取下一页
SELECT TOP 15 *
FROM operation_xm.dbo.rentHouse_info_tbl
WHERE id > 100  -- 直接定位到上一页末尾之后
ORDER BY id ASC;
```
这种方式避免扫描前置数据，适合超深分页，但仅支持“下一页”，不支持“跳页”。

### 1.5. 总结
`OFFSET ... FETCH` 是一种简洁、标准的分页方式，**适合中等数据量、分页深度不大的场景**（如 Web 列表的前几十页）。使用时需注意配合 `ORDER BY` 保证顺序，并在超深分页场景下考虑替代方案以优化性能。

## 2. TOP 分页

`TOP` 是 SQL Server 中用于限制查询结果返回行数的关键字，常与子查询结合实现分页功能。相比 `OFFSET ... FETCH`，`TOP` 分页是更早出现的分页方式，在 SQL Server 2012 之前是实现分页的主要手段。下面从原理、逻辑、应用场景到具体示例进行详细说明。

### 2.1. 分页原理与逻辑
`TOP` 分页的核心原理是**通过两次 `TOP` 嵌套，先获取前 N 行，再反向取其中的 M 行**，从而实现“跳过指定行数，获取目标页数据”的效果。具体逻辑拆解如下：

1. **外层 `TOP M`**：表示“最终要获取的每页行数”（M 为每页显示的记录数）。
2. **内层 `TOP N`**：表示“从结果集开头取前 N 行”（N 为“当前页起始位置 + 每页行数”）。
3. **反向排序**：内层按指定字段排序后，外层按同一字段反向排序，再取 `TOP M`，相当于“从内层的 N 行中剔除前 (N-M) 行”。
4. **依赖 `ORDER BY`**：必须配合 `ORDER BY` 保证结果集顺序固定，否则分页会出现数据混乱。

**逻辑公式**：
`第 X 页数据 = 外层取 TOP M 行（按字段反向排序），内层取 TOP (X*M) 行（按字段正向排序）`
（M 为每页行数，X 为目标页码）

### 2.2. 应用场景与适用条件
`TOP` 分页适用于以下场景：

1. **兼容低版本 SQL Server**
   由于 `OFFSET ... FETCH` 是 SQL Server 2012 才引入的语法，在 SQL Server 2008 及更早版本中，`TOP` 分页是唯一选择。

2. **数据量较小或分页深度较浅的场景**
   适合数据总量少（如几万行）或只需要前几页数据的场景，此时性能与 `OFFSET ... FETCH` 差异不大。

3. **简单的“上一页/下一页”需求**
   无需复杂的跳页功能（如直接跳至第 100 页），仅需按顺序浏览数据时，`TOP` 分页可满足基本需求。

4. **对查询语法兼容性要求高的系统**
   某些老旧系统或 ORM 框架可能默认生成 `TOP` 分页语法，为保持兼容性需继续使用。

### 2.3. 使用案例示例
仍以租房信息表 `rentHouse_info_tbl` 为例（结构同上），演示 `TOP` 分页的具体用法：

#### 2.3.1. 获取第 1 页数据（1-15 条）

需求：按发布时间倒序（最新在前），获取前 15 条租房信息（第 1 页）。
**说明**：第 1 页无需嵌套，直接用 `TOP 15` 即可。
```sql
SELECT TOP 15 *
FROM operation_xm.dbo.rentHouse_info_tbl
ORDER BY publish_time DESC;  -- 按发布时间降序（最新的先展示）
```

#### 2.3.2. 获取第 2 页数据（16-30 条）

需求：延续第 1 页的排序，获取第 2 页 15 条数据。
**逻辑**：内层先取前 30 条（2 页 × 15 条），外层反向排序后取前 15 条，即得到 16-30 条。
```sql
SELECT TOP 15 *
FROM (
    -- 内层：取前 30 条（第 1-30 条）
    SELECT TOP 30 *
    FROM operation_xm.dbo.rentHouse_info_tbl
    ORDER BY publish_time DESC  -- 内层正向排序（与外层反向）
) AS temp
ORDER BY publish_time ASC;  -- 外层反向排序，取前 15 条即第 16-30 条
```

#### 2.3.3. 获取第 3 页数据（31-45 条）

需求：按租金升序（便宜的先展示），获取第 3 页 15 条数据。
**逻辑**：内层取前 45 条（3 页 × 15 条），外层反向排序后取前 15 条。
```sql
SELECT TOP 15 *
FROM (
    -- 内层：取前 45 条（第 1-45 条）
    SELECT TOP 45 *
    FROM operation_xm.dbo.rentHouse_info_tbl
    ORDER BY price ASC  -- 内层按租金升序
) AS temp
ORDER BY price DESC;  -- 外层按租金降序，取前 15 条即第 31-45 条
```

#### 2.3.4. 结合筛选条件的分页

需求：筛选“面积大于 50㎡”的房源，按发布时间倒序，获取第 2 页（16-30 条）。
```sql
SELECT TOP 15 *
FROM (
    -- 内层：先筛选再取前 30 条（符合条件的第 1-30 条）
    SELECT TOP 30 *
    FROM operation_xm.dbo.rentHouse_info_tbl
    WHERE area > 50  -- 筛选条件
    ORDER BY publish_time DESC  -- 内层正向排序
) AS temp
ORDER BY publish_time ASC;  -- 外层反向排序，取第 16-30 条
```

#### 2.3.5. 动态控制页码和每页行数（变量方式）
实际开发中，页码和每页行数通常由用户输入，可通过变量动态计算：
```sql
DECLARE @PageSize INT = 20;  -- 每页行数
DECLARE @PageNum INT = 4;    -- 目标页码（第 4 页）

SELECT TOP (@PageSize) *
FROM (
    -- 内层取前 (PageNum × PageSize) 条
    SELECT TOP (@PageNum * @PageSize) *
    FROM operation_xm.dbo.rentHouse_info_tbl
    ORDER BY id ASC  -- 按主键升序（唯一且稳定）
) AS temp
ORDER BY id DESC;  -- 外层反向排序，取第 (PageNum-1)*PageSize+1 到 PageNum*PageSize 条
```

### 2.4. 优缺点与适用边界

#### 2.4.1. 优点：

1. 兼容性好：支持所有 SQL Server 版本，尤其适合老旧系统。
2. 实现简单：语法直观，适合初学者理解和使用。
3. 小数据量性能稳定：在数据量小（如 10 万行以内）或分页深度浅（如前 10 页）时，性能表现良好。

#### 2.4.2. 缺点：

1. 语法冗余：相比 `OFFSET ... FETCH`，嵌套子查询的写法更繁琐，维护成本高。
2. 超深分页效率低：与 `OFFSET ... FETCH` 类似，当页码过大时（如第 1000 页），内层需扫描大量数据，性能下降明显。
3. 排序逻辑易出错：内层和外层的排序方向必须相反，否则会导致数据错误，新手容易忽略这一点。
4. 使用内层和外层的排序方向相反的方式取值，会导致最后取出的数据排序并没有按需求排序（示例中要求按发布时间倒序显示，但最后因为取反排序的原因导致最后结果变成了正序显示），这样导致还要对最后展示结果再进行一次内层排序才能按需求正常展示，<mark>上面示例中都能取到正确的数据，但显示的排序是有问题的</mark>

#### 2.4.3.  与 `OFFSET ... FETCH` 的对比：

- 当使用 SQL Server 2012 及以上版本时，优先选择 `OFFSET ... FETCH`，语法更简洁，逻辑更清晰。
- 若需兼容低版本（如 SQL Server 2008），则必须使用 `TOP` 分页。

### 2.5. 总结
`TOP` 分页是通过“嵌套 `TOP` + 反向排序”实现的传统分页方式，**最适合兼容低版本 SQL Server 或数据量较小的场景**。其核心是通过内层取“前 N 行”、外层反向取“前 M 行”来跳过指定数据。在现代 SQL Server 环境中，`OFFSET ... FETCH` 已逐渐替代 `TOP` 分页，但理解 `TOP` 分页的原理仍有助于掌握 SQL 分页的底层逻辑。

除了 `TOP` 分页和 `OFFSET ... FETCH` 分页，SQL 中还有两种常用的分页方式：**基于“游标定位”的分页**（也叫“ Keyset Pagination ”或“ 书签分页 ”）和 **ROW_NUMBER() 分页**。这两种方式各有独特的原理和适用场景，下面分别详细介绍。

## 3. 基于“游标定位”的分页（Keyset Pagination）

### 3.1. 原理与逻辑

这种分页方式的核心是**通过“唯一标识字段”（通常是主键或唯一索引字段）直接定位到上一页的末尾，从而获取下一页数据**，无需扫描前置数据。其逻辑基于“连续的唯一值”实现跳转，类似用书签标记阅读位置。

- **核心思路**：以上一页最后一条数据的“唯一标识值”为条件，直接查询“大于该值”的下一批数据。
- **依赖条件**：必须有一个**唯一且有序的字段**（如自增主键 `id`、时间戳 `create_time` 等），且该字段需建立索引以提升查询效率。

**逻辑公式**：
`第 X 页数据 = WHERE 唯一字段 > 上一页最后一条数据的唯一值 ORDER BY 唯一字段 LIMIT 每页行数`

### 3.2. 应用场景与适用条件

- **超深分页场景**：如需要查询第 1000 页、第 10000 页数据时，性能远优于 `OFFSET ... FETCH` 或 `TOP` 分页（无需扫描大量前置数据）。
- **数据量极大的表**：百万级、千万级数据量的表，如日志表、订单表等。
- **仅需“下一页”功能的场景**：如移动端的“下拉加载更多”，无需跳页（如直接到第 50 页）功能。
- **数据实时性要求高的场景**：可避免因数据新增/删除导致的前后页数据重复或遗漏。

### 3.3. 使用案例示例

以租房信息表 `rentHouse_info_tbl` 为例（主键 `id` 为自增唯一值），演示基于 `id` 的游标定位分页：

#### 3.3.1. 获取第 1 页数据（1-15 条）

```sql
-- 按 id 升序（默认自增顺序），取前 15 条，记录最后一条的 id（假设为 15）
SELECT TOP 15 *
FROM operation_xm.dbo.rentHouse_info_tbl
ORDER BY id ASC;
```

#### 3.3.2. 获取第 2 页数据（16-30 条）

以上一页最后一条数据的 `id=15` 为条件，查询 `id>15` 的下 15 条：
```sql
-- 基于上一页最后一个 id=15，直接定位到下一页起点
SELECT TOP 15 *
FROM operation_xm.dbo.rentHouse_info_tbl
WHERE id > 15  -- 关键：通过 id 定位，跳过前 15 条
ORDER BY id ASC;
```

#### 3.3.3. 结合排序字段的游标分页

若按非主键字段（如 `publish_time`）排序，需确保该字段与主键组合唯一（避免排序重复）：
```sql
-- 第 1 页：按发布时间倒序（最新在前），取前 15 条，记录最后一条的 publish_time 和 id
SELECT TOP 15 *
FROM operation_xm.dbo.rentHouse_info_tbl
ORDER BY publish_time DESC, id DESC;  -- 组合排序确保唯一性

-- 第 2 页：以上一页最后一条的 publish_time='2023-10-01' 和 id=200 为条件
SELECT TOP 15 *
FROM operation_xm.dbo.rentHouse_info_tbl
WHERE publish_time < '2023-10-01'  -- 时间早于上一页最后一条
   OR (publish_time = '2023-10-01' AND id < 200)  -- 时间相同则 id 更小的
ORDER BY publish_time DESC, id DESC;
```

### 3.4. 优缺点

- **优点**：
  1. 超深分页性能极佳，直接通过索引定位，无需扫描前置数据。
  2. 数据实时性好，不会因中间数据变化导致重复或遗漏。
  3. 适合大数据量表的分页查询。

- **缺点**：
  1. 不支持“跳页”功能（如直接到第 100 页），只能通过上一页的末尾值获取下一页。
  2. 依赖唯一有序字段，且需为该字段建立索引，否则性能下降。
  3. 多字段排序时，条件写法复杂（需处理等值情况）。

## 4. ROW_NUMBER() 分页

### 4.1. 原理与逻辑

`ROW_NUMBER()` 是 SQL Server 2005 及以上版本支持的窗口函数，它能为结果集中的每行分配一个唯一的序号（从 1 开始）。利用这个特性，可通过“筛选序号范围”实现分页。

- **核心思路**：先为所有数据按指定字段排序并分配序号，再查询序号在 `(X-1)*M+1` 到 `X*M` 范围内的行（X 为页码，M 为每页行数）。

**逻辑公式**：
`第 X 页数据 = WHERE 序号 BETWEEN (X-1)*M + 1 AND X*M`

### 4.2. 应用场景与适用条件

- **需要灵活跳页的场景**：支持“首页、末页、第 N 页”等任意跳转，功能与 `OFFSET ... FETCH` 类似。  
- **SQL Server 2005-2008 版本**：这些版本不支持 `OFFSET ... FETCH`，`ROW_NUMBER()` 是比 `TOP` 分页更简洁的替代方案。
- **中等数据量场景**：数据量在几十万行以内，分页深度不超过 100 页时，性能表现稳定。

### 4.3. 使用案例示例

仍以 `rentHouse_info_tbl` 为例，按 `publish_time` 倒序分页：

#### 4.3.1. 获取第 1 页数据（1-15 条）
```sql
SELECT *
FROM (
    -- 内层：为每行分配序号（按发布时间倒序）
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY publish_time DESC) AS row_num
    FROM operation_xm.dbo.rentHouse_info_tbl
) AS temp
WHERE row_num BETWEEN 1 AND 15;  -- 筛选第 1-15 条
```

#### 4.3.2. 获取第 3 页数据（31-45 条）
```sql
DECLARE @PageSize INT = 15;
DECLARE @PageNum INT = 3;

SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY price ASC) AS row_num  -- 按租金升序排序
    FROM operation_xm.dbo.rentHouse_info_tbl
    WHERE area > 50  -- 结合筛选条件
) AS temp
WHERE row_num BETWEEN (@PageNum - 1) * @PageSize + 1 
                  AND @PageNum * @PageSize;  -- 31-45 条
```

### 4.4. 优缺点

- **优点**：
  1. 语法比 `TOP` 分页更直观，无需嵌套反向排序，逻辑清晰。
  2. 支持任意跳页，功能完整，适合 Web 端分页组件。
  3. 兼容 SQL Server 2005+，比 `OFFSET ... FETCH` 支持更早的版本。

- **缺点**：
  1. 超深分页性能差：与 `OFFSET ... FETCH` 类似，需扫描大量数据生成序号，页码越大效率越低。
  2. 不支持“获取总页数”：需额外查询总条数（`COUNT(*)`），增加性能开销。

## 5. 四种分页方式对比与选择建议

**选择建议**：
1. 若使用 SQL Server 2012+ 且数据量中等（百万级以内），优先选 `OFFSET ... FETCH`，兼顾简洁性和功能完整性。
2. 若数据量极大（千万级以上）或需超深分页，必选**游标定位分页**，牺牲跳页功能换取性能。
3. 若需兼容 SQL Server 2005-2008，选 `ROW_NUMBER()` 分页，比 `TOP` 分页更易维护。
4. 仅在必须兼容 SQL Server 2000 等极老版本时，才考虑 `TOP` 分页，并注意排序修正。