oracle rank()函数总结

oracle rank()函数总结（http://xsb.itpub.net/post/419/44634更全面）

首先,要了解rank在英语的意思：等级.也就是说这是一个给数据确定等级的函数.

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

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

 

 

 

 

 

 

SELECT
      RANK('SHENZHEN',2007,5,50000)  WITHIN GROUP
      (ORDER BY area_code,year,month,saleroom) Rank
FROM T_SALE

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.

 

转自：http://keke-wanwei.iteye.com/blog/138632

 

其实现在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,有了这个字段,就很容易得到前三名的销售员了.

新问题:销售额50000块在深圳,2007年5月能排到第几?

 

可以的。在9i后增加了合计功能 如 

SELECT RANK(4,3) WITHIN GROUP (ORDER BY col1,col2) "Rank" FROM table;