《SQL 入门教程》第05篇 限定结果数量

《SQL 入门教程》专栏目录

• 第01篇 SQL 简介
• 第02篇 查询初体验
• 第03篇 查询条件
• 第04篇 结果排序
• 第05篇 限定结果数量
• 第06篇 分组与汇总
• 第07篇 多表连接查询
• 第08篇 初探 SQL 查询执行顺序
• 第09篇 CASE：SQL 中的条件表达式
• 第10篇 子查询
• 第11篇 集合运算
• 第12篇 简单视图
• 第13篇 数据操作语言
• 第14篇 数据类型
• 第15篇 维护表结构
• 示例数据库
• SQL 完整性约束
• 实战 SQL：实现百度、高德等地图中的地铁换乘线路查询
• 实战 SQL：微信、微博等社交网络中的友好、粉丝关系分析
• 实战 SQL：销售数据的小计/合计/总计以及数据透视表
• 实战 SQL：银行等金融机构可疑支付交易的监测
• 实战 SQL：亚马逊、京东等电商平台的销售排行榜和飙升榜

---

本篇介绍 SQL 标准中对查询结果进行限制的OFFSET子句和FETCH子句，以及六种主流数据库中的实现及差异：Oracle、MySQL、Microsoft SQL Server、PostgreSQL、Db2、SQLite。

如果一个表包含几千行数据，但是我们只想查看其中的一部分，比如前 10 行；或者，想要查看从第 11 行开始的 5 行，需要用到OFFSET子句和FETCH子句。

常见这类的应用包括 Top-N 查询和分页查询。

Top-N 查询

由于不同数据库的实现存在较大差异，本篇先以 Oracle 12c 语法为例，最后给出不同数据库的实现语法和示例。先来看一个示例：

SELECT first_name, last_name, salary
  FROM employees
 ORDER BY salary DESC
 FETCH FIRST 10 ROWS ONLY;

以上查询返回薪水最高的前 10 位员工。首先，ORDER BY子句定义了按照薪水从高到低排序；然后FETCH子句指定了只返回前 10 条记录。结果如下：

分页查询

考虑另一个场景，假如应用提供了分页显示的功能，每页显示 10 条记录，点击下一页时，需要显示第 11 到第 20 条记录。

SELECT first_name, last_name, salary
  FROM employees
 ORDER BY salary DESC
OFFSET 10 ROWS
 FETCH FIRST 10 ROWS ONLY;

先看结果：

返回的数据跳过了薪水最高的 10 条记录，这就是OFFSET子句的效果。

我们看看 SQL 标准中的完整定义：

SELECT column1, column2, ...
  FROM table
[WHERE conditions]
[ORDER BY column1 ASC, column2 DESC, ...]
[OFFSET m {ROW | ROWS}]
[FETCH { FIRST | NEXT } [ num_rows | n PERCENT ] { ROW | ROWS } { ONLY | WITH TIES }];

其中，OFFSET表示偏移量，即从第 m+1 行开始返回；如果不指定，从第 1 行开始返回。

FETCH用于指定返回多少行，FIRST和NEXT等价；num_rows 表示行数，n PERCENT 表示即按照百分比指定行数，ROW和ROWS等价；ONLY和WITH TIES的差别在于，如果最后存在更多排名相同的数据行，WITH TIES会返回更多的数据。

以下查询按照百分比返回前10%的数据：

SELECT first_name, last_name, salary
  FROM employees
 ORDER BY salary DESC
 FETCH FIRST 10 PERCENT ROWS ONLY;

因为 employees 总共包含 107 条记录，因此查询会返回 11 条记录。

再看一个WITH TIES的示例：

SELECT first_name, last_name, salary
  FROM employees
 ORDER BY salary DESC
 FETCH FIRST 10 PERCENT ROWS WITH TIES;

结果返回了 13 条记录，因为第 12 行和第 13 行数据的薪水（11000）和第 11 行一样，WITH TIES也会返回这些额外的数据。

数据库	限定结果数量	描述
Oracle	✔️	Oracle 12c开始提供了遵循SQL标准的语法，对于更早的版本可以使用 ROWNUM 实现，参考表格下面的示例
MySQL	✔️	MySQL使用不同的LIMIT语法，参考表格下面的示例： LIMIT num_rows OFFSET m LIMIT [m,] num_rows
SQL Server	✔️	对于兼容SQL标准的语法，SQL Sever不支持按照百分比指定行数，也不支持 WITH TIES SQL Server还支持TOP查询语法，参考表格下面的示例： TOP (expression) [PERCENT] [ WITH TIES ]
PostgreSQL	✔️	对于兼容SQL标准的语法，PostgreSQL不支持按照百分比指定行数，也不支持 WITH TIES PostgreSQL还支持LIMIT语法，参考表格下面的示例： LIMIT { count | ALL } OFFSET start
Db2	✔️	对于兼容SQL标准的语法，Db2不支持按照百分比指定行数，也不支持 WITH TIES Db2还兼容MySQL的LIMIT语法，参考表格下面的示例： LIMIT num_rows OFFSET m LIMIT [m,] num_rows
SQLite	✔️	SQLite使用不同的LIMIT语法，参考表格下面的示例： LIMIT num_rows OFFSET m LIMIT [m,] num_rows

其他语法

以下是其他形式的实现和语法。Oracle 11g 以及之前的版本可以使用子查询实现。

-- Oracle 11g and before example
SELECT first_name, last_name, salary
  FROM (SELECT first_name, last_name, salary, rownum AS rn
          FROM (SELECT first_name, last_name, salary
                  FROM employees
                 ORDER BY salary DESC
               )
       )
 WHERE rn >= 5 AND rn < 15; -- return from 5th to 14th

MySQL 中使用LIMIT和OFFSET语法实现。

-- MySQL example
SELECT first_name, last_name, salary
  FROM employees
 ORDER BY salary DESC
 LIMIT 5, 10; -- return from 5th to 14th
 -- LIMIT 10 OFFSET 5;

SQL Server 中的TOP语法。

-- SQL Server TOP example
SELECT TOP(10) PERCENT WITH TIES
       first_name, last_name, salary
  FROM employees
 ORDER BY salary DESC;

PostgreSQL 中的LIMIT和OFFSET语法实现。

-- PostgreSQL LIMIT example
SELECT first_name, last_name, salary
  FROM employees
 ORDER BY salary desc
 LIMIT 10 OFFSET 5; -- return from 5th to 14th

Db2 中的LIMIT和OFFSET语法实现。

-- Db2 LIMIT example
SELECT first_name, last_name, salary
  FROM employees
 ORDER BY salary DESC
 LIMIT 5, 10; -- return from 5th to 14th
 -- LIMIT 10 OFFSET 5;

SQLite 中的LIMIT和OFFSET语法实现。

-- SQLite example
SELECT first_name, last_name, salary
  FROM employees
 ORDER BY salary DESC
 LIMIT 5, 10; -- return from 5th to 14th
 -- LIMIT 10 OFFSET 5;