oracle 学习笔记 - 伪列 rownum

今天药忘吃喽~ 2022-06-10 12:54 238阅读 0赞

### 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作为外层的普通字段