oracle中聚合函数rank、dense_rank和row_rumber的使用

 

oracle中聚合函数rank、dense_rank和row_rumber的使用

2008-11-21 10:51

其实现在Oracle对于rank和dense_rank已经支持合计功能，不过这次我仅仅使用了其分析功能。具体语法如下：RANK ( ) OVER ( [query_partition_clause] order_by_clause )。 下面给出一些来自网上的示例： TABLE：S （subject，mark） 数学，80 语文，70 数学，90 数学，60 数学，100 语文，88 语文，65 语文，77 现在我想要的结果是：每门科目的前3名的分数 数学，100 数学，90 数学，80 语文，88 语文，77 语文，70 那么语句就这么写： select * from (select rank() over(partition by subject order by mark desc) rk,S.* from S) T where T.rk<=3; dense_rank与rank()用法相当，但是有一个区别：dence_rank在处理相同的等级时，等级的数值不会跳过。rank则跳过。 例如：表     A B C a          liu          wang a          jin          shu a          cai          kai b          yang      du b          lin          ying b          yao        cai b          yang      99 例如：当rank时为： select m.a,m.b,m.c,rank() over(partition by a order by b) liu from test3 m A          B             C          LIU a          cai          kai          1 a          jin           shu        2 a          liu           wang     3 b          lin           ying        1 b          yang      du           2 b          yang      99           2 b          yao        cai           4 而如果用dense_rank时为： select m.a,m.b,m.c,dense_rank() over(partition by a order by b) liu from test3 m A          B             C          LIU a          cai          kai          1 a          jin           shu        2 a          liu           wang     3 b          lin           ying        1 b          yang      du           2 b          yang      99           2 b          yao        cai           3 使用Rank()函数进行排名,假如排在第一,第二位的同学分数相同,则他们并列第一,他们下一位则是第三。而使用Dense_Rank()排名是不间断的,既第一第二位同学并列第一,第三位同学是第二。我们可以根据需求所提的排名规则来相应地选用排名函数。 得到每个部门薪水最高的三个雇员： select t.deptno,t.rank,t.sal from ( select emp.*,row_number() over (partition by deptno order by sal desc) row_number, --1,2,3    rank() over (partition by deptno order by sal desc) rank, --1,1,3    dense_rank() over (partition by deptno order by sal desc) dense_rank from emp --1,1,2 ) t where t.rank<=3 结果如下: 10    5000.00    7839    1    1    1 10    2450.00    7782    2    2    2 10    1300.00    7934    3    3    3 20    3000.00    7788    1    1    1 20    3000.00    7902    2    1    1 20    2975.00    7566    3    3    2 20    1100.00    7876    4    4    3 20    800.00    7369    5    5    4 30    2850.00    7698    1    1    1 30    1600.00    7499    2    2    2