Oracle 伪列: ROWNUM 应用与总结

1. ROWNUM 原理

ROWNUM 是根据 SQL 查询出的结果给每行分配一个逻辑编号, 每行数据会因为输出的顺序不同而获得不同的逻辑编号, 编号是从 1 开始的;

SELECT empno, ROWNUM FROM emp;  -- 查询 scott 用于 emp 表中, empno 和对应的 ROWNUM

-- 错误演示:
SELECT e.ROWNUM FROM emp;
-- 此种写法是错误的!

-- 以下是所有错误范例
-- 错误 1: 
SELECT * FROM emp WHERE ROWNUM BETWEEN 2 AND 5;

-- 错误 2: 
SELECT * FROM emp WHERE ROWNUM >= 2 AND ROWNUM <= 5;

解答: 为什么不能直接在 WHERE 子句中, 使用: ROWNUM BETWEEN 2 AND 5 ?

2. ROWNUM 应用

2.1 Top N 结果输出

-- 输出结果的前 N 条数据, 可以通过 ROWNUM 是实现
SELECT empno FROM emp WHERE ROWNUM < 5; -- 输出前 4 条数据

2.2 分页查询

分页查询是经常用到的一种应用需求
本文演示 3 种实现方式:

-- way 1: 
SELECT * FROM (
    SELECT e.*, ROWNUM rn FROM emp e) a
WHERE a.rn BETWEEN 2 AND 5;
-- 效率最低

-- way 2: 
SELECT e.*, ROWNUM rn FROM emp e WHERE ROWNUM <=5
MINUS
SELECT e.*, ROWNUM rn FROM emp e WHERE ROWNUM <=1;
-- 效率较高

-- way 3: 
SELECT * FROM (
    SELECT e.*, ROWNUM rn FROM emp WHERE ROWNUM <=5) a
WHERE a.rn>=2;
-- 效率最高

2.3 分组子排序

  -- 暂时没有实现

请参看, CONAN ZONE 博客中的总结: 利用ROWNUM做分组子排序

3. 参考文档

• Oracle中ROWNUM的使用技巧 本文主要参考此博文中, 博主在博文中更加系统地分析了每种 SQL 语句的运行效率, 和一些高级应用主题;
• Oracle 伪列总结, 本文主要是简单的总结了 Oracle 中的基本概念和基本使用, 适合快速入门;