row_number over 函数

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行。