SQL从入门到出门 第6篇 限定结果数量

本篇介绍SQL:2016（ISO/IEC 9075:2016）标准中定义的用于对查询结果进行限制的OFFSET子句和FETCH子句，以及六种主流数据库中的实现及差异：Oracle、MySQL、Microsoft SQL Server、PostgreSQL、Db2、SQLite。

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

常见这类的应用包括 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	OK	Oracle 12c开始提供了遵循SQL标准的语法，对于更早的版本可以使用 ROWNUM 实现，参考表格下面的示例
MySQL	OK	MySQL使用不同的LIMIT语法，参考表格下面的示例： LIMIT num_rows OFFSET m LIMIT [m,] num_rows
SQL Server	OK	对于兼容SQL标准的语法，SQL Sever不支持按照百分比指定行数，也不支持 WITH TIES SQL Server还支持TOP查询语法，参考表格下面的示例： TOP (expression) [PERCENT] [ WITH TIES ]
PostgreSQL	OK	对于兼容SQL标准的语法，PostgreSQL不支持按照百分比指定行数，也不支持 WITH TIES PostgreSQL还支持LIMIT语法，参考表格下面的示例： LIMIT { count | ALL } OFFSET start
Db2	OK	对于兼容SQL标准的语法，Db2不支持按照百分比指定行数，也不支持 WITH TIES Db2还兼容MySQL的LIMIT语法，参考表格下面的示例： LIMIT num_rows OFFSET m LIMIT [m,] num_rows
SQLite	OK	SQLite使用不同的LIMIT语法，参考表格下面的示例： LIMIT num_rows OFFSET m LIMIT [m,] num_rows

-- 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 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 example
SELECT TOP(10) PERCENT WITH TIES
       first_name, last_name, salary
  FROM employees
 ORDER BY salary DESC;

-- 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 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 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;