oracle 学习笔记 - 伪列 rownum

level

层次查询中有应用。

rownum

用来指示行号

rownum两点注意事项

SQL> --关于rownum
SQL> --1. rownum永远按照默认的顺序生成
SQL> --    默认顺序是没有排序时候的顺序，即是对数据进行排序，行号仍然不变
SQL> --    即:名字为 SMITH 的记录行号任然为1。
SQL> --2. rownum只能使用< <=;不能使用> >=
SQL> select rownum,empno,ename,sal from emp order by sal desc;

    ROWNUM      EMPNO ENAME             SAL                                                                                                                                                             
-------------------------------------------                                                                                                                                                             
         9       7839 KING             5000                                                                                                                                                             
        13       7902 FORD             3000                                                                                                                                                             
         8       7788 SCOTT            3000                                                                                                                                                             
         4       7566 JONES            2975                                                                                                                                                             
         6       7698 BLAKE            2850                                                                                                                                                             
         7       7782 CLARK            2450                                                                                                                                                             
         2       7499 ALLEN            1600                                                                                                                                                             
        10       7844 TURNER           1500                                                                                                                                                             
        14       7934 MILLER           1300                                                                                                                                                             
         3       7521 WARD             1250                                                                                                                                                             
         5       7654 MARTIN           1250                                                                                                                                                             

    ROWNUM      EMPNO ENAME             SAL                                                                                                                                                             
-------------------------------------------                                                                                                                                                             
        11       7876 ADAMS            1100                                                                                                                                                             
        12       7900 JAMES             950                                                                                                                                                             
         1       7369 SMITH             800                                                                                                                                                             

已选择 14 行。

rownum 在 top-n 问题中的应用

-- 一般不在子查询排序；但top-n分析问题中，必须对子查询排序
select rownum,empno,ename,sal
  2  from (select * from emp order by sal desc)
  3  where rownum<=3;

    ROWNUM      EMPNO ENAME             SAL                                                                                                                                                             
--------------------------------------------                                                                                                                                                          
         1       7839 KING             5000                                                                                                                                                             
         2       7788 SCOTT            3000                                                                                                                                                             
         3       7902 FORD             3000   


-- 思路分析：

    1.由于rownum随着表的生成而生成，即是对表进行排序，rownum还是不会变的，因此下面语句是错误的。    
    select * from emp where rownum<=3 order by sal desc 

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
------------------------------------------------------------------------------------------
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
      7369 SMITH      CLERK           7902 17-12月-80            800                    20

    2.正确的思路：
       将排序之后的数据看做一张新表，这张新表中的rownum是有意义的，与top-n是一致的，因此有如下代码：

    select rownum,empno,ename,sal
    2  from (select * from emp order by sal desc)
    3  where rownum<=3;

rownum在oracle分页中的使用

需求：分页显示行号 5 - 8 的数据

    ROWNUM      EMPNO ENAME             SAL
----------------------------------------------
         1       7369 SMITH             800
         2       7499 ALLEN            1600
         3       7521 WARD             1250
         4       7566 JONES            2975
         5       7654 MARTIN           1250
         6       7698 BLAKE            2850
         7       7782 CLARK            2450
         8       7788 SCOTT            3000
         9       7839 KING             5000
        10       7844 TURNER           1500
        11       7876 ADAMS            1100
        12       7900 JAMES             950
        13       7902 FORD             3000
        14       7934 MILLER           1300

注意：rownum只能使用< 、<=;不能使用>、 >=
解决思路：仍然需要构造新表，生成合适的rownum。
解决办法：两层嵌套，先排序，最内层为排序机制，这里使用rownum默认排序机制

SQL> select rownum ,empno,ename,sal
  2  from (select rownum r,empno,ename,sal
  3    from (select * from emp ) e1
  4    where rownum <=8) e2
  5  where r>=5;

    ROWNUM      EMPNO ENAME             SAL
---------- ---------- ---------- ----------
         1       7654 MARTIN           1250
         2       7698 BLAKE            2850
         3       7782 CLARK            2450
         4       7788 SCOTT            3000


SQL> select rownum,r,empno,ename,sal
  2  from (select rownum r,empno,ename,sal
  3    from emp where rownum<=8)
  4  where r>=5;

    ROWNUM          R      EMPNO ENAME             SAL
---------- ---------- ---------- ---------- ----------
         1          5       7654 MARTIN           1250
         2          6       7698 BLAKE            2850
         3          7       7782 CLARK            2450
         4          8       7788 SCOTT            3000

总结：将内层中的rownum作为外层的普通字段