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