Oracle分析函数总结 - 排序 - rank,dense_rank,row_number,first

Oracle企业 2012-08-08

这几个函数区别是:
a)rank()是跳跃排序,有两个第1名时接下来就是第3名;
b)dense_rank()是连续排序,有两个第1名时接下来仍然跟着第2名;
c)row_number()是连续排序,并且有并列名次时,按照记录集中记录的顺序名次依次递增。

  1. www.linuxidc.com @ORCL1> select ename, sal, deptno,  
  2.   2  rank() over(partition by deptno order by sal) rank,  
  3.   3  dense_rank() over(partition by deptno order by sal) dense_rank,  
  4.   4  row_number() over(partition by deptno order by sal) row_number  
  5.   5   from emp;  
  6.   
  7. ENAME             SAL     DEPTNO       RANK DENSE_RANK ROW_NUMBER  
  8. ---------- ---------- ---------- ---------- ---------- ----------   
  9. MILLER       $1300.00         10          1          1          1  
  10. CLARK        $2450.00         10          2          2          2  
  11. KING         $5000.00         10          3          3          3  
  12. SMITH         $800.00         20          1          1          1  
  13. ADAMS        $1100.00         20          2          2          2  
  14. JONES        $2975.00         20          3          3          3  
  15. SCOTT        $3000.00         20          4          4          4  
  16. FORD         $3000.00         20          4          4          5  
  17. JAMES         $950.00         30          1          1          1  
  18. MARTIN       $1250.00         30          2          2          2  
  19. WARD         $1250.00         30          2          2          3  
  20. TURNER       $1500.00         30          4          3          4  
  21. ALLEN        $1600.00         30          5          4          5  
  22. BLAKE        $2850.00         30          6          5          6  
  23.   
  24. 14 rows selected.  
2) first(), last()
first,last需要和dense_rank结合使用,返回排在第一和最后的记录(集合)。语法:
aggregate_function KEEP (dense_rank first|last order by ...) [over([partition_clause])]

例如,查询各部门薪水最高和最低的人名。不带over子句, 作为聚合函数使用。
  1. www.linuxidc.com @ORCL1> column first format a20  
  2. www.linuxidc.com @ORCL1> column last format a20  
  3. www.linuxidc.com @ORCL1> select deptno,  
  4.   2  wm_concat(ename) keep (dense_rank first order by sal descfirst,  
  5.   3  wm_concat(ename) keep (dense_rank last order by sal desclast  
  6.   4  from emp group by deptno;  
  7.   
  8.     DEPTNO FIRST                LAST  
  9. ---------- -------------------- --------------------   
  10.         10 KING                 MILLER  
  11.         20 SCOTT,FORD           SMITH  
  12.         30 BLAKE                JAMES  

查询各部门薪水最高和最低的人名。带over子句, 作为分析函数使用。

  1. www.linuxidc.com @ORCL1> select ename, sal, deptno,  
  2.   2  wm_concat(ename) keep (dense_rank first order by sal desc)  
  3.   3      over(partition by deptno) first,  
  4.   4  wm_concat(ename) keep (dense_rank last order by sal desc)  
  5.   5      over(partition by deptno) last  
  6.   6  from emp;  
  7.   
  8. ENAME             SAL     DEPTNO FIRST                LAST  
  9. ---------- ---------- ---------- -------------------- ---------   
  10. CLARK        $2450.00         10 KING                 MILLER  
  11. KING         $5000.00         10 KING                 MILLER  
  12. MILLER       $1300.00         10 KING                 MILLER  
  13. JONES        $2975.00         20 FORD,SCOTT           SMITH  
  14. FORD         $3000.00         20 FORD,SCOTT           SMITH  
  15. ADAMS        $1100.00         20 FORD,SCOTT           SMITH  
  16. SMITH         $800.00         20 FORD,SCOTT           SMITH  
  17. SCOTT        $3000.00         20 FORD,SCOTT           SMITH  
  18. WARD         $1250.00         30 BLAKE                JAMES  
  19. TURNER       $1500.00         30 BLAKE                JAMES  
  20. ALLEN        $1600.00         30 BLAKE                JAMES  
  21. JAMES         $950.00         30 BLAKE                JAMES  
  22. BLAKE        $2850.00         30 BLAKE                JAMES  
  23. MARTIN       $1250.00         30 BLAKE                JAMES  
  24.   
  25. 14 rows selected.  

相关推荐

ganyouxianjava / 0评论 2012-05-31