关闭

rank()与dense_rank()的分析

227人阅读 评论(0) 收藏 举报

1.rank()分析函数

语法:
RANK() OVER([PARTITION BY expr1[,expr2,..,exprn]] ORDER BY expr1[,expr2,..,exprn])

   功能:                                                                                                                                            先将记录按PARTITION分组,组内再以ORDER BY排序,算出当前记录在组内所处的级别(名次). PRATITION条件省略时,表明不分组,或将全录记录作为一组

例子: 有表如下:

SQL> select * from test;

NAME       SUBJECT         SCORE
---------- ---------- ----------
aa         chinese            90
aa         art                     88
aa         english            80
bb         chinese            80
bb         art                     92
bb         english            96
cc         chinese            73
cc         art                      78
cc         english            60

已选择9行。

执行语句如下:

SQL> break on name skip 1;
SQL> select * from ( select name ,subject,score ,rank() over ( partition by name order by score desc)
 score_rank  from  test)  where score_rank<=3;

NAME       SUBJECT         SCORE SCORE_RANK
---------- ---------- ---------- ----------
aa         chinese            90          1
                 art                88          2
           english            80          3

bb         english            96          1
                 art                92          2
           chinese            80          3

cc            art                78          1
           chinese            73          2
           english            60          3

已选择9行。

 

SQL> select * from (select name ,subject,score ,rank() over ( partition by subject order by score desc) score_rank from test) where score_rank<=3;

NAME       SUBJECT         SCORE SCORE_RANK
---------- ---------- ---------- ----------
bb         art                92          1
aa                            88          2
cc                            78          3

aa         chinese     90          1
bb                            80          2
cc                            73          3

bb         english     96          1
aa                            80          2
cc                            60          3

已选择9行。

2.dense_rank函数

DENSE_RANK分析函数

语法:DENSE_RANK() OVER([PARTITION BY expr1[,expr2,..,exprn]] ORDER BY expr1[,expr2,..,exprn])

 


SQL> insert into test values('dd','chinese',80);

已创建 1 行。

SQL> insert into test values('dd','art',78);

已创建 1 行。

SQL> insert into test values('dd','english',96);

已创建 1 行。

SQL> select * from (select name ,subject,score,dense_rank() over ( partition by subject order by score desc) score_rank from test) where score_rank<=3;



NAME       SUBJECT         SCORE SCORE_RANK
---------- ---------- ---------- ----------
dd         art                96          1
bb                            92          2
aa                            88          3
aa         chinese            90          1
bb                            80          2
cc                            73          3
bb         english            96          1
aa                            80          2
cc                            60          3

已选择9行。

SQL>  select * from (select name ,subject,score ,dense_rank() over ( partition by name order by score desc) score_rank from test) where score_rank<=3;

NAME       SUBJECT         SCORE SCORE_RANK
---------- ---------- ---------- ----------
aa         chinese            90          1
aa         art                     88          2
aa         english            80          3
bb                                 96          1
bb         art                    92          2
bb         chinese            80          3
cc         art                     78          1
cc         chinese            73          2
cc         english            60          3
dd         art                   96          1
dd                               80          2
dd                               78          3

已选择12行。

SQL> select * from (select name ,subject,score,rank() over ( partition by name order by score desc)
score_rank from test) where score_rank<=3;

NAME       SUBJECT         SCORE SCORE_RANK
---------- ---------- ---------- ----------
aa         chinese            90          1
aa         art                     88          2
aa         english            80          3
bb                                  96          1
bb         art                      92          2
bb         chinese            80          3
cc         art                      78          1
cc         chinese            73          2
cc         english            60          3
dd         art                96          1
dd                            80          2
dd                            78          3

已选择12行。

SQL> select * from (select name ,subject,score,rank() over ( partition by subject order by score desc) score_rank from test) where score_rank<=3;

NAME       SUBJECT         SCORE SCORE_RANK
---------- ---------- ---------- ----------
dd         art                96          1
bb                            92          2
aa                            88          3
aa         chinese      90          1
bb                            80          2
cc                            73          3
bb         english       96          1
aa                            80          2
cc                            60          3

已选择9行。

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:851次
    • 积分:47
    • 等级:
    • 排名:千里之外
    • 原创:1篇
    • 转载:0篇
    • 译文:3篇
    • 评论:0条
    文章存档