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


作者: 不剪发的Tony老师
毕业于北京航空航天大学,十多年数据库管理与开发经验,目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址:https://tonydong.blog.csdn.net

文章目录

    • 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 不会成立,因此不会返回任何数据,需要再加上一层嵌套。