Oracle里想实现SELECT TOP N，哪种写法最实用好用？

在Oracle数据库中,没有一个像Microsoft SQL Server里那样的直接叫做TOP N的关键字，Oracle提供了几种非常灵活且强大的方法来实现完全相同的功能，甚至在某些方面比TOP N更强大，根据不同的使用场景，最实用好用的写法可以归纳为以下两种主要方法，其中第二种是目前公认的最佳实践。

使用ROWNUM（传统方法，适用于简单场景）

这是Oracle早期版本中最常用、最广为人知的方法。ROWNUM是Oracle为查询结果集的每一行分配的一个伪列，它代表该行在结果集中的行号，从1开始。

最基本的写法（用于直接取前N条）：

如果你想查询工资最高的前5名员工,可以这样写：

SELECT *
FROM (
    SELECT *
    FROM employees
    ORDER BY salary DESC
)
WHERE ROWNUM <= 5;

为什么需要子查询？ 这是一个关键点，因为ROWNUM是在数据被检索和排序之前就被分配的，如果你直接写SELECT * FROM employees WHERE ROWNUM <= 5 ORDER BY salary DESC;，Oracle会先取出任意5条记录（因为WHERE先执行），然后再对这5条记录进行排序，这显然得不到正确的结果，必须先用一个子查询完成排序，然后在外部查询中通过ROWNUM来限制条数。

分页查询的写法（非常实用）：

ROWNUM在实现分页功能时非常经典，假设每页显示10条记录，要查询第3页（即第21条到第30条）的数据，写法如下：

SELECT *
FROM (
    SELECT a.*, ROWNUM rn
    FROM (
        SELECT *
        FROM employees
        ORDER BY hire_date DESC
    ) a
    WHERE ROWNUM <= 30  -- 页数 * 每页条数
)
WHERE rn > 20;  -- (页数-1) * 每页条数

这个三层嵌套的查询是标准做法：

• 最内层子查询（a）负责排序。
• 中间层子查询在排序的基础上,通过ROWNUM <= 30取出前30条，并同时为这30条记录生成一个固定的行号别名rn。
• 最外层查询再从这个固定的结果集中,通过rn > 20“截取”出第21到第30条，实现分页。

ROWNUM方法的优缺点：

• 优点：语法简单，易于理解，尤其是在Oracle 12c之前的版本中，是唯一通用的方法，对于简单的TOP N查询，它非常直接。
• 缺点：语法略显繁琐，尤其是在做分页时，需要多层嵌套，可读性会变差，在更复杂的查询中（比如涉及多个表连接并需要分页时），写法会变得非常混乱。

使用FETCH FIRST/NEXT（现代方法，推荐使用）

从Oracle 12c Release 1 (12.1) 开始，Oracle引入了标准的ANSI SQL语法OFFSET ... FETCH ...，这可以说是实现TOP N和分页功能的“终极武器”，也是目前最实用、最好用的写法。

直接取前N条（最简单）：

同样是查询工资最高的前5名员工,新写法简洁到令人惊叹：

SELECT *
FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;

这行代码的意思一目了然：“从employees表中查询，按工资降序排列，然后只获取最前面的5行”，它的可读性远远超过了ROWNUM的嵌套写法。

取前N%的记录：

这个方法还有一个强大的功能,可以按百分比取数据，你想取工资最高的前10%的员工：

SELECT *
FROM employees
ORDER BY salary DESC
FETCH FIRST 10 PERCENT ROWS ONLY;

这是ROWNUM方法很难优雅实现的功能。

包含并列情况（WITH TIES）：

这是一个非常有用且独特的特性,假设你想取工资最高的前5名，但如果第5名有多个工资相同的员工，使用ROWS ONLY会随机舍弃掉并列的员工，而使用WITH TIES，则会把所有并列第5名的员工都包括进来。

SELECT *
FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS WITH TIES;

这样,你得到的结果可能不止5行，但能确保不遗漏任何符合“前5名”条件的记录。

分页查询（同样简洁）：

实现分页（第3页，每页10条）也变得异常简单：

SELECT *
FROM employees
ORDER BY hire_date DESC
OFFSET 20 ROWS  -- 跳过前20行
FETCH NEXT 10 ROWS ONLY;  -- 获取接下来的10行

OFFSET子句明确指出了要跳过的行数，FETCH子句指出了要获取的行数，逻辑清晰，代码简洁。

FETCH FIRST/NEXT方法的优缺点：

• 优点：
  • 符合SQL标准：与其他现代数据库（如PostgreSQL, DB2）的语法一致，便于迁移和理解。
  • 语法极其简洁明了：可读性远超ROWNUM。
  • 功能强大：天然支持百分比获取和处理并列情况。
• 缺点：

  仅适用于Oracle 12c及更高版本，如果你的数据库版本是11g或更早，则无法使用。

总结与选择建议

综合来看,选择哪种写法作为“最实用好用”的标准，主要取决于你的Oracle数据库版本：

• 如果你的数据库是Oracle 12c或更新版本，那么毫无悬念，应该首选FETCH FIRST/NEXT语法。 它简单、强大、标准，是所有新项目的必然选择，无论是简单的TOP N查询，还是复杂的分页，它都是最优雅、最高效的解决方案。

• 如果你的数据库是11g或更老的版本，那么ROWNUM是唯一现实的选择。 尽管语法嵌套较多，但它非常稳定且被广泛支持，尤其是在维护遗留系统时，你必须掌握这种方法。

对于当今大多数环境而言,FETCH FIRST/NEXT写法是Oracle中实现SELECT TOP N最实用、最好用的方法，它直接解决了用户的需求，并将代码的简洁性和表达力提升到了一个新的高度。