加载中…
个人资料
shirleywong
shirleywong
  • 博客等级:
  • 博客积分:0
  • 博客访问:7,941
  • 关注人气:0
  • 获赠金笔:0支
  • 赠出金笔:0支
  • 荣誉徽章:
相关博文
推荐博文
谁看过这篇博文
加载中…
正文 字体大小:

Oracle中聚合函数rank()和dense_rank()的用法总结

(2007-11-02 11:17:20)
标签:

学习公社

Oracle中聚合函数rank()和dense_rank()的用法总结

聚合函数RANK 和 dense_rank 主要的功能是计算一组数值中的排序值。

在9i版本之前,只有分析功能(analytic ),即从一个查询结果中计算每一行的排序值,是基于order_by_clause子句中的value_exprs指定字段的。
其语法为:
RANK ( ) OVER ( [query_partition_clause] order_by_clause )

在9i版本新增加了合计功能(aggregate),即对给定的参数值在设定的排序查询中计算出其排序值。这些参数必须是常数或常值表达式,且必须和ORDER BY子句中的字段个数、位置、类型完全一致。
其语法为:
RANK ( expr [, expr]... ) WITHIN GROUP
( ORDER BY
  expr [ DESC | ASC ] [NULLS { FIRST | LAST }]
  [, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...
)

 

下面写几个例子给大家帮助大家更好地理解函数用法。

例子1:有一成绩表score表的数据结构如下。

stuno   varchar2(10)   学号

subject  varchar2(10)  科目

score   number         分数

 

向score表填充数据。

stuno    subject    score

1001      语文       111

1001      数学       123

1001      英语        21

1002      语文        76

1003      数学        33

1002      英语       150

1004      数学        89

1003      语文        97

1002      数学        89

1004      语文        90

 

分析功能:列出subject分组后根据score排序,并生成数字列。比较实用于在成绩表中查出各科前几名的信息。
SELECT a.*,RANK() OVER(PARTITION BY subject ORDER BY score desc) "Rank" FROM score a;
结果如下:
stuno     subject    score     Rank

1001       数学       123        1

1004       数学        89        2

1002       数学        89        2

1003       数学        33        4

1002       英语       150        1

1001       英语        21        2

1001       语文       111        1

1003       语文        97        2

1004       语文        90        3

1002       语文        76        4

 

            列表1

这时,我们在对比看下,如果将以上的查询语句中的rank()换成dense_rank()

会得到什么样的结果。

SELECT a.*,dense_rank() OVER(PARTITION BY subject ORDER BY score desc) "Rank" FROM score a;
结果如下:

stuno     subject    score     Rank

1001       数学       123        1

1004       数学        89        2

1002       数学        89        2

1003       数学        33        3

1002       英语       150        1

1001       英语        21        2

1001       语文       111        1

1003       语文        97        2

1004       语文        90        3

1002       语文        76        4

            列表2

会很容易发现:学号为1003的科目为数学的这条记录的rank列变成了3.

因此,可以这么说,dense_rank与rank()用法相当,但是有一个区别:dense_rank在并列关系是,相关等级不会跳过。rank则跳过。

 

例2:

那么,有时候,我们想统计每门科目的前三名学生的成绩。

应该这么写:

select * from (select rank() over(partition by subject order by score desc) rk,score.* from score) t
where t.rk<=3;

结果如下:

stuno     subject    score     Rank

1001       数学       123        1

1004       数学        89        2

1002       数学        89        2

1003       数学        33        4

1002       英语       150        1

1001       英语        21        2

1001       语文       111        1

1003       语文        97        2

1004       语文        90        3

              列表3

我们可从结果中发现,语文科目的少了条记录。排名第4的没有了。

 

例子3:

合计功能:如果我们想计算出subject为语文的,score为97的这个学员他在

列表1的结果集中顺序是第几个,可以这么写:

SELECT RANK('语文',76) WITHIN GROUP(ORDER BY subject,score desc) "Rank" FROM score;

结果如下:

Rank

10

 

 

 

 

 

 

0

阅读 评论 收藏 转载 喜欢 打印举报/Report
  • 评论加载中,请稍候...
发评论

    发评论

    以上网友发言只代表其个人观点,不代表新浪网的观点或立场。

      

    新浪BLOG意见反馈留言板 电话:4000520066 提示音后按1键(按当地市话标准计费) 欢迎批评指正

    新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 会员注册 | 产品答疑

    新浪公司 版权所有