oracle分析函数row_number() over()使用

谁借莪１个温暖的怀抱￠ 2022-07-20 15:21 223阅读 0赞

row\_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的).  
  与rownum的区别在于：使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序，而此函数在包含排序从句后是先排序再计算行号码．  
  
  row\_number()和rownum差不多，功能更强一点（可以在各个分组内从1开时排序）．  
  
  rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）．  
  
  dense\_rank()l是连续排序，有两个第二名时仍然跟着第三名。相比之下row\_number是没有重复值的 ．  
  
  lag（arg1,arg2,arg3):  
arg1是从其他行返回的表达式  
arg2是希望检索的当前行分区的偏移量。是一个正的偏移量，时一个往回检索以前的行的数目。  
arg3是在arg2表示的数目超出了分组的范围时返回的值。  
  
看几个SQL语句:  
  
语句一:  
  
select row\_number() over(order by sale/cnt desc) as sort, sale/cnt  
from (  
select -60 as sale,3 as cnt from dual union  
select 24 as sale,6 as cnt from dual union  
select 50 as sale,5 as cnt from dual union  
select -20 as sale,2 as cnt from dual union  
select 40 as sale,8 as cnt from dual);  
  
执行结果:  
  
          SORT       SALE/CNT  
\---------- ----------  
             1             10  
             2              5  
             3              4  
             4            -10  
             5            -20  
  
  
语句二:查询员工的工资,按部门排序  
  
select ename,sal,row\_number() over (partition by deptno order by sal desc) as sal\_order from scott.emp;  
  
执行结果:  
  
ENAME                           SAL      SAL\_ORDER  
\-------------------- ---------- ----------  
KING                           5000              1  
CLARK                          2450              2  
MILLER                         1300              3  
SCOTT                          3000              1  
FORD                           3000              2  
JONES                          2975              3  
ADAMS                          1100              4  
SMITH                           800              5  
BLAKE                          2850              1  
ALLEN                          1600              2  
TURNER                         1500              3  
WARD                           1250              4  
MARTIN                         1250              5  
JAMES                           950              6  
  
已选择14行。  
  
语句三:查询每个部门的最高工资  
  
select deptno,ename,sal from  
     (select deptno,ename,sal,row\_number() over (partition by deptno order by sal desc) as sal\_order  
         from scott.emp) where sal\_order <2;  
  
执行结果:  
  
       DEPTNO ENAME                          SAL  
\---------- -------------------- ----------  
           10 KING                          5000  
           20 SCOTT                         3000  
           30 BLAKE                         2850  
  
已选择3行。  
  
语句四：  
  
select deptno,sal,rank() over (partition by deptno order by sal) as rank\_order from scott.emp order by deptno;  
  
执行结果:  
  
     DEPTNO         SAL RANK\_ORDER  
\---------- ---------- ----------  
         10        1300           1  
         10        2450           2  
         10        5000           3  
         20         800           1  
         20        1100           2  
         20        2975           3  
         20        3000           4  
         20        3000           4  
         30         950           1  
         30        1250           2  
         30        1250           2  
         30        1500           4  
         30        1600           5  
         30        2850           6  
  
已选择14行。  
  
语句五：  
  
select deptno,sal,dense\_rank() over(partition by deptno order by sal) as dense\_rank\_order from scott.emp order by deptn;  
执行结果：  
  
     DEPTNO         SAL DENSE\_RANK\_ORDER  
\---------- ---------- ----------------  
         10        1300                 1  
         10        2450                 2  
         10        5000                 3  
         20         800                 1  
         20        1100                 2  
         20        2975                 3  
         20        3000                 4  
         20        3000                 4  
         30         950                 1  
         30        1250                 2  
         30        1250                 2  
         30        1500                 3  
         30        1600                 4  
         30        2850                 5  
  
已选择14行。