ROWNUM含义
- ROWNUM 是一个伪列,对查询返回的行编号即行号,由1开始逐次递增;
- 关键点:Oracle的rownum 数值是在获取每行之后才赋予的;
举例说明:
SQL> selectrownum,ename,job,sal from emp where rownum=1;
ROWNUM ENAME JOB SAL
-------------------- --------- ---------
1 SMITH CLERK 800.00
SQL> selectrownum,ename,job,sal from emp where rownum=2;
ROWNUM ENAME JOB SAL
-------------------- --------- ---------
SQL> selectrownum,ename,job,sal from emp where rownum<=6;
ROWNUM ENAME JOB SAL
-------------------- --------- ---------
1 SMITH CLERK 800.00
2 ALLEN SALESMAN 1760.00
3 WARD SALESMAN 1250.00
4 JONES MANAGER 2975.00
5 MARTIN SALESMAN 1250.00
6 BLAKE MANAGER 2850.00
SQL> selectrownum,ename,job,sal from emp where rownum between 2 and 6;
ROWNUM ENAME JOB SAL
-------------------- --------- ---------
SQL> selectrownum,ename,job,sal from emp where rownum between 1 and 6;
ROWNUM ENAME JOB SAL
-------------------- --------- ---------
1 SMITH CLERK 800.00
2 ALLEN SALESMAN 1760.00
3 WARD SALESMAN 1250.00
4 JONES MANAGER 2975.00
5 MARTIN SALESMAN 1250.00
6 BLAKE MANAGER 2850.00
总结:有上述几个例子可以说明,rownum的运行机制
1)、首先从表里面查询出数据,然后将数据进行编号(编号始终是从1开始的)
2)、编完号后,再通过where后面的查询条件对数据进行过滤
当rownum=1时,正好匹配,则将查询出的数据进行输出
当rownum=2时,无法匹配,则将编号为rownum=1的数据进行过滤,紧接着查询第二条数据,继续从1开始编号…...匹配……
分页
SQL> selectrn,ename,job,sal from (select rownum rn,ename,job,sal from emp whererownum<=6) where rn>=4;
RN ENAME JOB SAL
-------------------- --------- ---------
4 JONES MANAGER 2975.00
5 MARTIN SALESMAN 1250.00
6 BLAKE MANAGER 2850.00
解析:将select rownum rn,ename,job,sal from emp whererownum<=6 看做一个临时的中间表,从而可以实现将中间表中的数据随意取出。
先排序,再取出
SQL> selectrn,ename,job,sal from (select rownum rn,ename,job,sal from (selectename,job,sal from emp order by sal desc) where rownum<=6) where rn>=4;
RN ENAME JOB SAL
-------------------- --------- ---------
4 JONES MANAGER 2975.00
5 BLAKE MANAGER 2850.00
6 CLARK MANAGER 2450.00