Oracle 中 ORDER BY 后如何正确使用 ROWNUM

---

🧩 一、问题背景

在 Oracle SQL 中，我们经常需要对查询结果排序后再编号、分页或取前 N 条数据。 很多人第一反应是直接用 ROWNUM，例如：

SELECT ROWNUM, t.*
FROM emp t
ORDER BY sal DESC;

但结果往往不符合预期——ROWNUM 并没有按工资排序后的顺序生成。 这是为什么呢？

---

🧩 二、执行顺序原理

Oracle SQL 的逻辑执行顺序如下：

FROM -> WHERE -> SELECT -> ORDER BY

ROWNUM 是在 结果排序前（ORDER BY 之前） 生成的伪列。 这意味着：

• 先从表中取数据；
• 立即分配 ROWNUM；
• 最后才执行排序。

因此，上面的 SQL 实际是：

1. 先给每行分配了一个 ROWNUM；
2. 然后再根据工资排序；
3. 所以 ROWNUM 对应的顺序并不代表排序结果的顺序。

---

🧩 三、正确用法：子查询包裹

如果希望在 排序之后 再使用 ROWNUM， 必须先排序，再在外层查询中使用 ROWNUM。

SELECT ROWNUM AS rn, t.*
FROM (
    SELECT * FROM emp ORDER BY sal DESC
) t
WHERE ROWNUM <= 10;

✅ 这样写才是“排序后取前 10 条”的正确方式。

📘 原理： 内层子查询先执行 ORDER BY 排序， 外层再根据排好序的结果生成 ROWNUM。

---

🧩 四、Oracle 12c+ 新语法：FETCH / OFFSET

从 Oracle 12c 开始，官方提供了标准 SQL 分页语法， 可以完全替代 ROWNUM 写法，更直观、更高效。

✅ 取前 10 条：

SELECT *
FROM emp
ORDER BY sal DESC
FETCH FIRST 10 ROWS ONLY;

✅ 分页查询（跳过前 20 条，再取 10 条）：

SELECT *
FROM emp
ORDER BY sal DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

---

🧩 五、ROWNUM 的现代替代：ROW_NUMBER()

如果你只是想在排序后生成一个行号（编号）， 可以使用窗口函数 ROW_NUMBER()：

SELECT ROW_NUMBER() OVER (ORDER BY sal DESC) AS rn,
       e.*
FROM emp e;

这个函数是 在排序后生成行号， 不会受到 ROWNUM 的限制。

---

🧩 六、总结对比

需求场景	推荐写法	Oracle 版本
排序前编号	SELECT ROWNUM, ...	任意版本
排序后编号	子查询 + ROWNUM	任意版本
分页 / Top N	FETCH FIRST / OFFSET	12c+
排序后编号（窗口函数）	ROW_NUMBER() OVER (ORDER BY ...)	11g+

---

🧩 七、完整示例对比

-- ❌ 错误写法（rownum 在排序前）
SELECT ROWNUM, e.* FROM emp e ORDER BY sal DESC;

-- ✅ 正确写法（排序后编号）
SELECT ROWNUM, e.* FROM (SELECT * FROM emp ORDER BY sal DESC) e;

-- ✅ Oracle 12c+ 推荐写法
SELECT * FROM emp ORDER BY sal DESC FETCH FIRST 10 ROWS ONLY;

-- ✅ 使用窗口函数
SELECT ROW_NUMBER() OVER (ORDER BY sal DESC) rn, e.* FROM emp e;

---

🧩 八、可复用分页模板

适用于项目中的分页查询：

-- Oracle 11g 及以下
SELECT * FROM (
    SELECT ROWNUM AS rn, t.*
    FROM (
        SELECT * FROM emp ORDER BY sal DESC
    ) t
    WHERE ROWNUM <= :offset + :limit
)
WHERE rn > :offset;

-- Oracle 12c+
SELECT *
FROM emp
ORDER BY sal DESC
OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY;

---

🧩 九、总结

• ROWNUM 是在排序之前生成的；
• 如果要按排序后的顺序取行号或分页，必须使用子查询或新语法；
• 现代 Oracle（12c+）推荐使用 FETCH / OFFSET；
• 如果要编号，请用 ROW_NUMBER() 窗口函数。

---

💡 最佳实践：

• Oracle 11g：使用子查询 + ROWNUM
• Oracle 12c+：使用 FETCH FIRST 或 ROW_NUMBER()
• 任何分页查询，都不要直接在含 ORDER BY 的查询中用裸 ROWNUM！