《Oracle 入门教程》第 07 篇 限制返回行数

文章目录

• • 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 条记录。

更多请见：http://www.mark-to-win.com/tutorial/51501.html