Oracle中rank和dens_rank方法用法

select a.*,rank()over(order by cj desc) pm
from(
??? (select 'a' name,1 cj
??? from dual
??? ) union(
??? select 'b' name,1 cj
??? from dual
??? ) union(
??? select 'c' name,2 cj
??? from dual
??? ) union(
??? select 'd' name,3 cj
??? from dual
??? ) union(
??? select 'e' name,3 cj
??? from dual
??? ) union(
??? select 'f' name,4 cj
??? from dual
??? ) union(
??? select 'g' name,0 cj
??? from dual
??? )
) a
order by pm

----------------------------------------------result---------
? NAME CJ? PM
??f?4?1
??d?3?2
??e?3?2
??c?2?4
??a?1?5
??b?1?5
??g?0?7
----------------------------------------------------------------------
　　首先,要了解rank在英语的意思：等级.也就是说这是一个给数据确定等级的函数.

　　以销售为例,有地区,年,月,销售员,销售额,记录这五个字段.我们可以按地区,年,月,销售额对销售员进行排序,这样对销售员来说就相当于有一个等级概念了,第一名就是销售最高的......,如果我们要找出每个地区,年,月,销售额的前三名销售员.SQL如何写?

　　Sql代码

　　SELECT area_code, YEAR, MONTH, saleroom,saler

　　RANK () OVER (PARTITION BY area_code,year ,month ORDER BY area_code,year,month,saleroom ) RANK

　　FROM t_sale

　　Sql代码

　　SELECT

　　RANK(‘SHENZHEN‘,2007,5,50000)? WITHIN GROUP

　　(ORDER BY area_code,year,month,saleroom) Rank

　　FROM T_SALE

　　上面这个SQL就可以搞定了.要注意的是,Rank()里的参数必须为常数,或常值表达式,里面参数的个数,类型也要和order by后字段的类型相对应.

　　上面就是Rank函数的两个用法.另外还有一个dense_rank(),它的用法和rank()一样,只是计算等级的方式不同.例如上面的

　　1,2,3,3,3,6.用dense_rank() 就是1,2,3,3,3,4.

　　其实现在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则跳过。 row_number() 补充；

　　例如：表

　　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

　　而如果用row_number()时为

　　select m.a,m.b,m.c,row_number() 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???? 3

　　b???? yao??? cai???? 4

　　现在RANK 就是1,2,3,3,3,6,有了这个字段,就很容易得到前三名的销售员了.
（非原创，以前收藏在txt文档里的一篇文章，放在这里防止丢了，^_^）