文章目录

• • 7.1 使用 FETCH 子句实现排行榜
  • 7.2 使用 ROWNUM 实现排行榜
  • 7.3 使用 OFFSET 子句分页查询
  • 7.4 使用 ROWNUM 实现分页查询

Top-N 排行榜是一个常见的业务需求，例如销量排行榜、电影排行榜等。另外，很多时候查询的结果可能包含成千上万行数据，但是在页面前端为了便于查看每次只显示一定数量的结果，也就是查询的分页功能。Oracle 提供了 SQL 标准中的FETCH和OFFSET子句，可以实现以上功能；另外，我们也可以使用 Oracle 中的伪列ROWNUM实现相同的效果。
7.1 使用 FETCH 子句实现排行榜

数据排行榜的本质就是先排序，然后再返回前 N 条记录。例如，以下语句查询薪水最高的 3 名员工：

SELECT emp_name, salary
FROM employee
ORDER BY salary DESC
FETCH FIRST 3 ROWS ONLY;
EMP_NAME|SALARY|
--------|------|
刘备     | 30000|
关羽     | 26000|
张飞     | 24000|

其中，ORDER BY 按照月薪从高到低进行排序，如果不指定排序的话不保证返回薪水最高的员工；FETCH 表示返回前 3 条数据，也就是月薪 Top-3 的员工；FIRST也可以写成NEXT，ROWS也可以写成ROW。

除了限制返回多少行外，FETCH 还可以按照百分比指定返回的数据量。完整的 FETCH 语法如下：

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

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

    OFFSET表示偏移量，即从第 m+1 行开始返回；如果不指定或者指定 0 或负数，表示从第 1 行开始返回；OFFSET 通常可以用于实现分页查询；
    FETCH指定返回多少行，FIRST 和 NEXT 作用相同；
    num_rows 表示按照行数计算返回的数据量，n PERCENT 表示按照百分比计算返回的数据量，ROW 和 ROWS 作用相同；
    ONLY和WITH TIES的区别在于如果最后有多个排名相同的数据行，WITH TIES 会返回更多的数据；默认为 ONLY。

例如，以下示例使用 WITH TIES 选项返回了更多的数据：

SELECT emp_name, salary
FROM employee
ORDER BY salary DESC
FETCH FIRST 3 ROWS WITH TIES;
EMP_NAME|SALARY|
--------|------|
刘备     | 30000|
关羽     | 26000|
张飞     | 24000|
诸葛亮    | 24000|

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

以下示例按照百分比返回月薪最高的前 10% 员工：

SELECT emp_name, salary
FROM employee
ORDER BY salary DESC
FETCH FIRST 10 PERCENT ROWS ONLY;
EMP_NAME|SALARY|
--------|------|
刘备     | 30000|
关羽     | 26000|
张飞     | 24000|

员工表中总共有 25 人，百分之十约为 3 人。我们同样可以使用 WITH TIES 返回更多数据。
7.2 使用 ROWNUM 实现排行榜

Oracle 11g 或者更早的版本中无法使用 FETCH 语句， 不过可以利用子查询和 ROWNUM 函数实现相同的功能。例如：

SELECT emp_name, salary, rownum AS rn
FROM (SELECT emp_name, salary
      FROM employee
      ORDER BY salary DESC
     ) emp_order
WHERE rownum <= 3;
EMP_NAME|SALARY|RN|
--------|------|--|
刘备     | 30000| 1|
关羽     | 26000| 2|
张飞     | 24000| 3|

其中，包含 ORDER BY 子句的 SELECT 语句是一个内部子查询，emp_order 是它的别名，它的作用是将员工按照月薪从高到低进行排序；外部的 SELECT 语句使用 ROWNUM 函数为排序后的数据指定一个数字编号，并且在查询条件中前 3 条记录，也就是水最高的前 3 名员工。

    📝我们将会在第 16 篇中介绍 Oracle 中的子查询。

子查询加 ROWNUM 函数的方式不支持按照百分比限制行数和 WITH TIES 选项。
7.3 使用 OFFSET 子句分页查询

前端页面通常都需要支持分页显示功能，也就是每页显示一定数量的记录（例如 10 行），同时提供“上一页”和“下一页”等导航按钮。这种分页查询的本质就是先跳过指定的行数，再返回 Top-N 记录，示意图如下：

实际上，Top-N 排行榜是分页查询的一个特殊情况。使用 SQL 实现这种功能需要引入另一个子句：OFFSET。

假设我们的应用提供了分页显示，每页显示 10 条记录。现在用户点击了下一页，需要显示第 11 到第 20 条记录。使用标准 SQL 语法实现如下：

SELECT emp_name, salary
FROM employee
ORDER BY salary DESC
OFFSET 10 ROWS
FETCH FIRST 10 ROWS ONLY;
EMP_NAME|SALARY|
--------|------|
关兴     |  7000|
关平     |  6800|
赵氏     |  6600|
廖化     |  6500|
张苞     |  6500|
孙丫鬟    |  6000|
赵统     |  6000|
马岱     |  5800|
简雍     |  4800|
孙乾     |  4700|

其中，ORDER BY 按照月薪从高到低进行排序；OFFSET 跳过 10 条数据；然后 FETCH 返回随后的 10 条数据。对于应用程序而言，先通过 COUNT() 函数获取数据的总行数，然后传入不同的OFFSET偏移量和FETCH数量，就可以在查询结果中任意导航。

    ⚠️随着OFFSET的增加，查询的性能会越来越差。因为服务器需要计算更多的偏移量，即使这些数据不需要被返回前端。

OFFSET 还可以用于实现其他功能。例如，以下语句可以找出月薪排名第 3 高的员工：

SELECT emp_name, salary
FROM employee
ORDER BY salary DESC
OFFSET 2 ROWS
FETCH FIRST 1 ROWS WITH TIES;
EMP_NAME|SALARY|
--------|------|
张飞     | 24000|
诸葛亮    | 24000|

7.4 使用 ROWNUM 实现分页查询

Oracle 11g 或者更早的版本也可以使用子查询和 ROWNUM 函数实现分页查询功能。例如：

SELECT emp_name, salary
FROM (SELECT emp_name, salary, rownum AS rn
      FROM (SELECT emp_name, salary
            FROM employee
            ORDER BY salary DESC
           ) emp_order
      WHERE rownum <= 20
     ) emp_rownum
WHERE rn >= 11;
EMP_NAME|SALARY|
--------|------|
关兴     |  7000|
关平     |  6800|
赵氏     |  6600|
廖化     |  6500|
张苞     |  6500|
赵统     |  6000|
孙丫鬟    |  6000|
马岱     |  5800|
简雍     |  4800|
孙乾     |  4700|

其中，最里层包含 ORDER BY 的子查询（emp_order）将员工按照月薪从高到低进行排序；中间的 SELECT 语句（emp_rownum）使用 ROWNUM 函数为排序后的数据指定一个数字编号，并且限制编号小于等于 20；最外层的查询返回编号大于等于 11 的数据。

注意，我们必须使用 3 个查询语句，而不能写成以下形式：

SELECT emp_name, salary, rownum AS rn
FROM (SELECT emp_name, salary
      FROM employee
      ORDER BY salary DESC
     ) emp_order
WHERE rownum >= 11 AND rownum <= 20;
EMP_NAME|SALARY|RN|
--------|------|--|

因为 ROWNUM 生成的数字从 1 开始，以上查询条件中的 rownum >= 11 不会成立，因此不会返回任何数据，需要再加上一层嵌套。