五种主流数据库：排行榜与分页查询

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

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

Top 排行榜

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

-- Oracle、Microsoft SQL Server 以及 PostgreSQLSELECT emp_name, salaryFROM employeeORDER BY salary DESCOFFSET 0 ROWSFETCH FIRST 5 ROWS ONLY;

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

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 以及 SQLiteSELECT emp_name, salaryFROM employeeORDER BY salary DESCLIMIT 5 OFFSET 0;

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

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

-- OracleSELECT * FROM (SELECT emp_name, salaryFROM employeeORDER BY salary DESC)WHERE rownum <= 5;

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

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

-- Microsoft SQL ServerSELECT TOP(5) emp_name, salaryFROM employeeORDER BY salary DESC;

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

分页查询

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

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

-- Oracle、Microsoft SQL Server 以及 PostgreSQLSELECT emp_name, salaryFROM employeeORDER BY salary DESCOFFSET 10 ROWSFETCH 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 选项。例如：

-- OracleSELECT emp_name, salaryFROM employeeORDER BY salary DESCFETCH FIRST 10 PERCENT ROWS ONLY;

查询返回的结果如下：

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

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

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

-- OracleSELECT emp_name, salaryFROM employeeORDER BY salary DESCFETCH FIRST 10 PERCENT ROWS WITH TIES;

查询返回的结果如下：

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

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

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

-- PostgreSQL SELECT emp_name, salaryFROM employeeORDER BY salary DESCFETCH 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 ServerSELECT TOP(10) PERCENT WITH TIESemp_name, salaryFROM employeeORDER BY salary DESC;emp_name|salary|--------+--------+刘备 |30000.00|关羽 |26000.00|张飞 |24000.00|诸葛亮 |24000.00|

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

-- OracleSELECT *FROM (SELECT emp_name, salary, rownum rnFROM (SELECT emp_name, salaryFROM employeeORDER BY salary DESC)WHERE rownum <= 20)WHERE rn > 10;

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

-- MySQL、PostgreSQL 以及 SQLiteSELECT emp_name, salaryFROM employeeORDER BY salary DESCLIMIT 10 OFFSET 10;

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

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

LIMIT m OFFSET nLIMIT n, m