浅谈oracle中rowid和rownum

[ 概要 ]

刚刚接触oracle的同学可能常常会被rowid和rownum这两个词弄混, 弄清楚这两个家伙对于我们写sql会有很大的帮助, 下面偶就抛砖引玉, 简单地谈谈他们之间的区别吧.

[ 比较 ]

rowid和rownum都是oracle中的伪列, 但他们还是存在本质区别:

rowid: 是物理地址, 用于定位数据表中数据的位置, 它是唯一的且不会改变.

rownum: 是根据查询的结果集给每行分配的一个逻辑编号, 查询结果不同, rownum自然不同. 

对于同一条记录, 查询条件不同, rownum会不同, 但是rowid将不变.

示例: 查询公司所有的员工

select rowid, rownum, empno, ename from emp;

示例: 查询公司员工姓名包含'S'

select rowid, rownum, empno, ename from emp where ename like '%S%';

细心的同学不难发现, 对于一行记录, rowid没变, rownum变了. 例如: 姓名为'SMITH'的员工.

[ 陷阱 ]

1. rownum只支持符号<、<=、!=, 不支持>, >=, =, between...and

select * from emp where rownum > 10 -- 没有结果

select *
  from (select rownum row_num, e.* from emp e) emp_temp
 where emp_temp.row_num > 10; -- 可以获取十位以后记录

解释一下: 因为rownum是对结果集加的一个伪列, 即先查到结果集之后再加上去的一个列(强调: 要有结果集). 简单地说rownum是符合条件结果的序列号, 它总是从1开始排位, 所以你选出的结果不可能没有1, 而有大于1的值.

有了上面的知识, 我们就不难解释以下现象:

① 为什么select * from emp where rownum != 10 总是返回前9条记录?

因为查询到结果集后, 显示完第9条记录, 之后的记录都是 != 10.

② 为什么rownum > 1 查不到一条记录, 而rownum > 0 或 rownum >= 1 却总显示所有记录?

因为rownum是在查询到结果集之后加上去的, 它总是从1开始.

③ 为什么between 1 and 10 或者 between 0 and 10 能查到结果, 而between 2 and 10却得不到结果?

原因同上, 少了rownum=1就像空中楼阁一样不能存在.

[ 应用 ]

废话扯了一箩筐, 这两哥们到底有啥用呢? 嘿嘿, 别急, 下面就上正菜...

问题: 获取薪水排名前三的员工

分析: 如果是sql server就好了, 我们可以使用top关键字, oracle中我们用rownum也能解决

 select *
   from (select * from emp order by sal desc)
  where rownum <= 3
  order by rownum asc