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-60assale,3ascntfromdualunion

select24assale,6ascntfromdualunion

select50assale,5ascntfromdualunion

select-20assale,2ascntfromdualunion

select 40 as sale,8 as cnt from dual);

执行结果:

          SORT       SALE/CNT

--------------------

110

25

34

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

----------------------------------------

KING50001

CLARK24502

MILLER13003

SCOTT30001

FORD30002

JONES29753

ADAMS11004

SMITH8005

BLAKE28501

ALLEN16002

TURNER15003

WARD12504

MARTIN12505

JAMES                           950              6

已选择14行。

语句三:查询每个部门的最高工资

select deptno,ename,sal from

(selectdeptno,ename,sal,row_number()over(partitionbydeptnoorderbysaldesc)assal_order

         from scott.emp) where sal_order <2;

执行结果:

       DEPTNO ENAME                          SAL

----------------------------------------

10KING5000

20SCOTT3000

           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

------------------------------

1013001

1024502

1050003

208001

2011002

2029753

2030004

2030004

309501

3012502

3012502

3015004

3016005

         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

------------------------------------

1013001

1024502

1050003

208001

2011002

2029753

2030004

2030004

309501

3012502

3012502

3015003

3016004

         30        2850                 5

已选择14行。

语句六：

select deptno,ename,sal,lag(ename,1,null) over(partition by deptno order by ename) as lag_ from scott.emp order by deptno;

执行结果：

     DEPTNO ENAME                        SAL LAG_

------------------------------------------------------------

10CLARK2450

10KING5000CLARK

10MILLER1300KING

20ADAMS1100

20FORD3000ADAMS

20JONES2975FORD

20SCOTT3000JONES

20SMITH800SCOTT

30ALLEN1600

30BLAKE2850ALLEN

30JAMES950BLAKE

30MARTIN1250JAMES

30TURNER1500MARTIN

         30 WARD                        1250 TURNER

已选择14行。