【赵渝强老师】Oracle查询结果集的行号 ROWNUM 详解

一、核心定义

ROWNUM 是伪列（不是真实表字段），对查询结果集的行动态分配序号，从1开始。

✅ 执行顺序关键点：

1. 读取一行
2. 应用 WHERE 过滤条件
3. 如果满足条件 → 分配 ROWNUM，ROWNUM+1
4. 如果不满足 → 丢弃该行，ROWNUM 不增加
5. ROWNUM 在 ORDER BY 排序之前生成！

点击这里查看视频讲解：【赵渝强老师】Oracle的行号：ROWNUM

---

二、基础示例

-- ✅ 取前5条
SQL> SELECT e.*, ROWNUM FROM emp e WHERE ROWNUM <= 5;

# 输出的信息如下：
EMPNO ENAME	 JOB		  MGR HIREDATE	  SAL  COMM DEPTNO     ROWNUM
----- ---------- --------- ---------- --------- ----- ----- ------ ----------
 7369 SMITH	 CLERK		 7902 17-DEC-80   800		20	    1
 7499 ALLEN	 SALESMAN	 7698 20-FEB-81  1600	300	30	    2
 7521 WARD	 SALESMAN	 7698 22-FEB-81  1250	500	30	    3
 7566 JONES	 MANAGER	 7839 02-APR-81  2975		20	    4
 7654 MARTIN	 SALESMAN	 7698 28-SEP-81  1250  1400	30	    5


-- ✅ 只取第1条
SQL> SELECT * FROM emp WHERE ROWNUM = 1;

# 输出的信息如下：
EMPNO ENAME	 JOB		  MGR HIREDATE	  SAL  COMM DEPTNO
----- ---------- --------- ---------- --------- ----- ----- ------
 7369 SMITH	 CLERK		 7902 17-DEC-80   800		20


-- ❌ 永远返回空！
SQL> SELECT * FROM emp WHERE ROWNUM > 1;

no rows selected

为什么 ROWNUM>1 无数据：
第一条数据拿到，ROWNUM=1，判断 1>1 不成立，丢弃；
下一行依旧尝试分配 ROWNUM=1，永远不会出现 ROWNUM=2。
同理 ROWNUM = 2、ROWNUM >=2 直接查不到数据。

---

三、高频踩坑：排序后取TopN

-- ❌ 错误！先分配ROWNUM，再排序，不是排序后的前5行
SQL> SELECT * FROM emp WHERE ROWNUM <=5 ORDER BY sal DESC;

# 输出的信息如下：
EMPNO ENAME	 JOB		  MGR HIREDATE	  SAL  COMM DEPTNO
----- ---------- --------- ---------- --------- ----- ----- ------
 7566 JONES	 MANAGER	 7839 02-APR-81  2975		20
 7499 ALLEN	 SALESMAN	 7698 20-FEB-81  1600	300	30
 7654 MARTIN SALESMAN	 7698 28-SEP-81  1250  1400	30
 7521 WARD	 SALESMAN	 7698 22-FEB-81  1250	500	30
 7369 SMITH	 CLERK		 7902 17-DEC-80   800		20


-- ✅ 正确写法：内层先排序，外层再生成ROWNUM
SQL> SELECT * FROM (
         SELECT * FROM emp ORDER BY sal DESC
     ) WHERE ROWNUM <= 5;
     
# 输出的信息如下：
EMPNO ENAME	 JOB		  MGR HIREDATE	  SAL  COMM DEPTNO
----- ---------- --------- ---------- --------- ----- ----- ------
 7839 KING	 PRESIDENT	      17-NOV-81  5000		10
 7788 SCOTT	 ANALYST	 7566 19-APR-87  3000		20
 7902 FORD	 ANALYST	 7566 03-DEC-81  3000		20
 7566 JONES	 MANAGER	 7839 02-APR-81  2975		20
 7698 BLAKE	 MANAGER	 7839 01-MAY-81  2850		30

四、经典分页（Oracle 11g及更早最常用双层子查询分页）

需求：查询第6～10条数据

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

# 输出的信息如下：
EMPNO ENAME	 JOB		  MGR HIREDATE	  SAL  COMM DEPTNO	   RN
----- ---------- --------- ---------- --------- ----- ----- ------ ----------
 7782 CLARK	 MANAGER	 7839 09-JUN-81  2450		10	    6
 7499 ALLEN	 SALESMAN	 7698 20-FEB-81  1600	300	30	    7
 7844 TURNER SALESMAN	 7698 08-SEP-81  1500	  0	30	    8
 7934 MILLER CLERK		 7782 23-JAN-82  1300		10	    9
 7521 WARD	 SALESMAN	 7698 22-FEB-81  1250	500	30	   10

执行流程：

1. 最内层：业务排序 ORDER BY
2. 中层：给排序好的结果生成行号别名 rn
3. 外层：根据别名 rn 截取分页范围

⚠️ 注意：别名 rn 不能写在中层的WHERE里，因为ROWNUM还没生成完成。

五、窗口函数 ROW_NUMBER()（10g及以上支持）

ROW_NUMBER() 是分析函数，先排序，再生成行号，支持分组编号 PARTITION BY

SQL> SELECT
          t.*,
          ROW_NUMBER() OVER(ORDER BY sal DESC) rn
      FROM emp t;

# 输出的信息如下：
EMPNO ENAME	 JOB		  MGR HIREDATE	  SAL  COMM DEPTNO	   RN
----- ---------- --------- ---------- --------- ----- ----- ------ ----------
 7839 KING	 PRESIDENT	      17-NOV-81  5000		10	    1
 7902 FORD	 ANALYST	 7566 03-DEC-81  3000		20	    2
 7788 SCOTT	 ANALYST	 7566 19-APR-87  3000		20	    3
 7566 JONES	 MANAGER	 7839 02-APR-81  2975		20	    4
 7698 BLAKE	 MANAGER	 7839 01-MAY-81  2850		30	    5
 7782 CLARK	 MANAGER	 7839 09-JUN-81  2450		10	    6
 7499 ALLEN	 SALESMAN	 7698 20-FEB-81  1600	300	30	    7
 7844 TURNER SALESMAN	 7698 08-SEP-81  1500	  0	30	    8
 7934 MILLER CLERK		 7782 23-JAN-82  1300		10	    9
 7521 WARD	 SALESMAN	 7698 22-FEB-81  1250	500	30	   10
 7654 MARTIN SALESMAN	 7698 28-SEP-81  1250  1400	30	   11
 7876 ADAMS	 CLERK		 7788 23-MAY-87  1100		20	   12
 7900 JAMES	 CLERK		 7698 03-DEC-81   950		30	   13
 7369 SMITH	 CLERK		 7902 17-DEC-80   800		20	   14

14 rows selected.

分页写法：

SQL> SELECT * FROM (
          SELECT
              t.*,
              ROW_NUMBER() OVER(ORDER BY sal DESC) rn
          FROM emp t
      ) WHERE rn BETWEEN 6 AND 10;
      
# 输出的信息如下：      
EMPNO ENAME	 JOB		  MGR HIREDATE	  SAL  COMM DEPTNO	   RN
----- ---------- --------- ---------- --------- ----- ----- ------ ----------
 7782 CLARK	 MANAGER	 7839 09-JUN-81  2450		10	    6
 7499 ALLEN	 SALESMAN	 7698 20-FEB-81  1600	300	30	    7
 7844 TURNER SALESMAN	 7698 08-SEP-81  1500	  0	30	    8
 7934 MILLER CLERK		 7782 23-JAN-82  1300		10	    9
 7521 WARD	 SALESMAN	 7698 22-FEB-81  1250	500	30	   10

六、Oracle12cR1+ 简化分页 FETCH / OFFSET

-- 取前5行
SELECT * FROM emp ORDER BY sal DESC FETCH FIRST 5 ROW ONLY;

-- 跳过10行，取10行（OFFSET从0开始）
SELECT * FROM emp ORDER BY sal DESC OFFSET 10 ROW FETCH NEXT 10 ROW ONLY;

七、ROWNUM 的重要限制

1. 不能加表别名：t.ROWNUM 语法报错，只能直接写 ROWNUM
2. ROWNUM 在 WHERE 阶段生成，ORDER BY 在WHERE之后，所以ROWNUM不受排序影响
3. 遇到 GROUP BY、DISTINCT：先分组/去重，再分配ROWNUM
4. 多表关联：ROWNUM是最终合并结果集的行号，不是单表独立行号
5. ROWNUM 是动态的，同一条数据两次查询，ROWNUM可能不一样（无排序时）

八、ROWNUM / ROW_NUMBER() / FETCH 对比

对象	类型	行号生成时机	支持分组编号	适用版本
ROWNUM	伪列	WHERE过滤后，ORDER BY之前	❌	全版本
ROW_NUMBER()	窗口函数	ORDER BY之后	✅ PARTITION BY	10g+
FETCH OFFSET	原生分页语法	排序之后	❌	12cR1+