欢迎大家来到IT世界,在知识的湖畔探索吧!

默认情况下，查询语句会返回满足过滤条件的所有数据。但是，有些时候我们只需查看其中的部分结果，常见的这类应用场景包括 Top-N 排行榜和数据分页查询。

本文比较五种主流数据库限定查询结果数量的实现和差异，包括 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite。

Top 排行榜

我们经常会看到各种 Top 排行榜，例如销量排行榜、十大热门游戏等。Top 排行榜的原理就是先对数据进行排序，然后返回前 N 条记录。SQL 标准定义了 FETCH 和 OFFSET 子句，可以用于限制返回结果的数量。例如，以下语句查找月薪排名前 5 的员工：

-- Oracle、Microsoft SQL Server 以及 PostgreSQL SELECT emp_name, salary FROM employee ORDER BY salary DESC OFFSET 0 ROWS FETCH FIRST 5 ROWS ONLY;

欢迎大家来到IT世界,在知识的湖畔探索吧!

其中，ORDER BY 子句表示按照月薪从高到低进行排序，OFFSET 子句表示跳过 0 行记录，FETCH 子句表示获取前 5 条记录，也就是月薪为 Top-5 的员工。查询返回的结果如下：

欢迎大家来到IT世界,在知识的湖畔探索吧!emp_name|salary --------|-------- 刘备 |30000.00 关羽 |26000.00 张飞 |24000.00 诸葛亮 |24000.00 赵云 |15000.00

目前只有 Oracle、Microsoft SQL Server 以及 PostgreSQL 支持这种标准语法。其中 Oracle 和 PostgreSQL 中的 OFFSET 子句可以省略，默认表示跳过 0 行记录。

除标准 SQL 外，还有一种常见的 LIMIT 子句也可以实现相同的功能，例如：

-- MySQL、PostgreSQL 以及 SQLite SELECT emp_name, salary FROM employee ORDER BY salary DESC LIMIT 5 OFFSET 0;

其中，ORDER BY 子句表示按照月薪从高到低进行排序，OFFSET 子句表示跳过 0 行记录，LIMIT 子句表示获取前 5 条记录，也就是月薪为 Top-5 的员工。MySQL、PostgreSQL 以及 SQLite 实现了这种 LIMIT 语法。另外，OFFSET 子句可以省略，默认表示跳过 0 行记录。

如果使用 Oracle 数据库，也可以利用 ROWNUM 伪列获取月薪最高的 5 名员工：

欢迎大家来到IT世界,在知识的湖畔探索吧!-- Oracle SELECT * FROM ( SELECT emp_name, salary FROM employee ORDER BY salary DESC ) WHERE rownum <= 5;

ROWNUM 伪列的作用就是对查询结果进行编号，序号从 1 开始递增。

如果使用 Microsoft SQL Server，我们也可以利用 TOP 关键字实现相同的功能。例如：

-- Microsoft SQL Server SELECT TOP(5) emp_name, salary FROM employee ORDER BY salary DESC;

其中，ORDER BY 子句表示按照月薪从高到低进行排序，TOP(5) 表示返回前 5 条记录。

分页查询

有时候查询的结果可能包含成千上万条记录，因此在前端显示时需要采用分页的方式，也就是每次只显示一定数量（例如 10 条记录）的结果，同时提供“下一页”、“上一页”等翻页按钮。这种分页查询的原理就是先跳过指定的行数，返回随后的 N 条记录。实际上，Top 排行榜是分页查询的一个特殊情况。

分页查询也有两种实现方式，第一种方式就是使用 SQL 标准中的 FETCH 和 OFFSET 子句。假如前端页面每次显示 10 名员工记录，现在用户点击了“第 2 页”，也就是返回第 11 条到第 20 条记录。我们可以使用以下查询语句：

-- Oracle、Microsoft SQL Server 以及 PostgreSQL SELECT emp_name, salary FROM employee ORDER BY salary DESC OFFSET 10 ROWS FETCH FIRST 10 ROWS ONLY;

其中，ORDER BY 子句表示按照月薪从高到低进行排序，OFFSET 子句表示跳过 10 条记录，FETCH 子句表示获取随后的 10 条记录。查询返回的结果如下：

emp_name|salary --------|------- 关兴 |7000.00 关平 |6800.00 赵氏 |6600.00 ... 简雍 |4800.00 孙乾 |4700.00

除以上基本的用法外，FETCH 子句还支持一些扩展选项，完整的语法如下：

[OFFSET m {ROW | ROWS}] FETCH {FIRST | NEXT} [num_rows | n PERCENT] {ROW | ROWS} {ONLY | WITH TIES};

其中，方括号（[ ]）表示可选项，大括号（{ }）表示必选项，竖线（|）表示二选一。每个
选项的作用如下：

• OFFSET 表示偏移量，即从第 m+1 行开始返回数据。默认偏移量为 0，表示从第 1 行开 始返回。ROW 和 ROWS 关键字等价。
• FETCH 表示返回多少数据，FIRST 和 NEXT 关键字等价。
• num_rows 表示以行数为单位限制返回的数据，n PERCENT 表示按照百分比限制返回的 数据，ROW 和 ROWS 关键字等价。
• ONLY 和 WITH TIES 的区别在于，最后如果有多个排名相同的记录，WITH TIES 会返 回更多的数据，ONLY 则不会返回更多的数据。

目前只有 Oracle 12c 以上版本完全支持 n PERCENT 和 WITH TIES 选项。例如：

-- Oracle SELECT emp_name, salary FROM employee ORDER BY salary DESC FETCH FIRST 10 PERCENT ROWS ONLY;

查询返回的结果如下：

EMP_NAME|SALARY --------|------ 刘备 | 30000 关羽 | 26000 张飞 | 24000

由于员工表中共有 25 名员工，10%约为 3 人。

以下查询使用了 WITH TIES 选项：

-- Oracle SELECT emp_name, salary FROM employee ORDER BY salary DESC FETCH FIRST 10 PERCENT ROWS WITH TIES;

查询返回的结果如下：

EMP_NAME|SALARY --------|------ 刘备 | 30000 关羽 | 26000 张飞 | 24000 诸葛亮 | 24000

由于“诸葛亮”和“张飞”的月薪相同，使用 WITH TIES 子句时返回了 4 条记录。

另外，PostgreSQL 13 开始支持 WITH TIES 选项。例如：

-- PostgreSQL SELECT emp_name, salary FROM employee ORDER BY salary DESC FETCH FIRST 3 ROWS WITH TIES; emp_name|salary | --------+--------+ 刘备 |30000.00| 关羽 |26000.00| 诸葛亮 |24000.00| 张飞 |24000.00|

Microsoft SQL Server 提供的 TOP 子句也支持 PERCENT 以及 WITH TIES 选项。例如：

-- Microsoft SQL Server SELECT TOP(10) PERCENT WITH TIES emp_name, salary FROM employee ORDER BY salary DESC; emp_name|salary | --------+--------+ 刘备 |30000.00| 关羽 |26000.00| 张飞 |24000.00| 诸葛亮 |24000.00|

对于 Oracle 数据库，如果使用 ROWNUM 伪列实现分页查询，需要嵌套两次子查询：

-- Oracle SELECT * FROM (SELECT emp_name, salary, rownum rn FROM ( SELECT emp_name, salary FROM employee ORDER BY salary DESC ) WHERE rownum <= 20 ) WHERE rn > 10;

第二种实现分页查询的方式就是利用 LIMIT 和 OFFSET 子句。例如：

-- MySQL、PostgreSQL 以及 SQLite SELECT emp_name, salary FROM employee ORDER BY salary DESC LIMIT 10 OFFSET 10;

其中，ORDER BY 子句表示按照月薪从高到低进行排序，OFFSET 子句表示跳过 10 条记录，LIMIT 子句获取随后的 10 条记录。

MySQL 和 SQLite 中以下两种语法的作用相同，注意偏移量 n 和返回行数 m 出现的顺序：

LIMIT m OFFSET n LIMIT n, m