浅析rownum
rownum 是一个伪列，当查询返回每一行的时候，会自动的添加一个伪列，标识oracle从表或者行 join 操作。中检索出的记录的顺序。oracle 检索出的第一条记录的rownum 是1,第二天记录的rownum 是2，依此类推。
SQL> select employee_id,last_name||' '||first_name as name
2 from hr.employees
3 where rownum <11;
EMPLOYEE_ID NAME
----------- --------------------
198 OConnell Donald
199 Grant Douglas
200 Whalen Jennifer
201 Hartstein Michael
202 Fay Pat
203 Mavris Susan
204 Baer Hermann
205 Higgins Shelley
206 Gietz William
100 King Steven
已选择10行。
SQL> select employee_id,last_name||' '||first_name as name
2 from hr.employees
3 where rownum <11
4 order by employee_id;
EMPLOYEE_ID NAME
----------- --------------------
100 King Steven
101 Kochhar Neena
102 De Haan Lex
103 Hunold Alexander
104 Ernst Bruce
105 Austin David
106 Pataballa Valli
107 Lorentz Diana
108 Greenberg Nancy
109 Faviet Daniel
已选择10行。
上面两条语句的结果不同，表明使用了order by子句以后oracle查询的时候使用了不同的检索方式。虽然rownum 是在排序以前生成的，但是order by 可能会改变oracle查询的时候检索信息的方式（使用了不同的或者使不使用索引与join 顺序）。
下面利用rownum 结合order by子句查询出工资最高的前10个员工的id,与工资。
SQL> select * from (select employee_id,salary
2 from hr.employees
3 order by salary desc)
4 where rownum<11;
EMPLOYEE_ID SALARY
----------- ----------
100 24000
101 17000
102 17000
145 14000
146 13500
201 13000
205 12000
147 12000
108 12000
168 11500
已选择10行。
当你在子查询中使用order by子句的时候，在父查询中使用rownum 条件过滤，构造一个top-N 报表。上面的查询中你使用的rownum 过滤条件是排序完以后重新生成的rownum。
SQL> select * from (select employee_id,salary
2 from hr.employees
3 order by salary desc)
4 where rownum>1;
未选定行
SQL> select * from (select employee_id,salary
2 from hr.employees
3 order by salary desc)
4 where rownum>10;
未选定行
从上面的测试可以知道使用rownum 大于一个正数为过滤条件总是查询失败的。但是却可以使用rownum=1为条件来查询。比如下面查询出的是工作最高的员工的id，工资。
SQL> select * from (select employee_id,salary
2 from hr.employees
3 order by salary desc)
4 where rownum=1;
EMPLOYEE_ID SALARY
----------- ----------
100 24000
SQL> select * from (select employee_id,salary
2 from hr.employees
3 order by salary desc)
4 where rownum=2;
未选定行
但是不能使用rownum=2或者其他正数,因为rownum 总是从 1 开始编号的，你给出一个rownum=2oracle 查询的时候取出的第一行rownum 是1 和 2 不匹配，所以取出的第一行将会被丢弃，再取出的一行 rownum又是 1 ，还是不匹配又丢弃再取，这样取出的行的rownum 总是 1.所以匹配失败。不能使用 rownum 大于某个正数的原理也是一样的，因为取出的第一行的 rownum不匹配，被丢弃了，以后再取的一行rownum 又是 1.所以查询失败。
SQL> create table test (id char(5),name varchar2(10));
表已创建。
SQL> insert into test values('10000','smith');
已创建 1 行。
SQL> insert into test values('10001','jane');
已创建 1 行。
SQL> commit;
提交完成。
SQL> select rownum,id,name
2 from test;
ROWNUM ID NAME
---------- ---------- --------------------
1 10000 smith
2 10001 jane
可以使用rownum给表中的每一行分配一个唯一值。
SQL> update test set id=rownum;
已更新2行。
SQL> select rownum,id,name
2 from test;
ROWNUM ID NAME
---------- ---------- --------------------
1 1 smith
2 2 jane
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26110315/viewspace-717495/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
以root用户运行命令
请登录后发表评论
登录
全部评论
