# rank()与dense_rank()的分析

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分析函数

 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行。

#### 分析函数-DENSE_RANK

2016-09-30 12:01:17

#### 一个SQL语句分清楚RANK(),DENSE_RANK(),ROW_NUMBER()三个排序的不同

2016-07-14 00:21:25

#### RANK、DENSE_RANK以及ROW_NUMBER区别

2016-04-20 16:54:52

#### rank,dense_rank,row_number使用和区别

2017-10-18 09:57:02

#### Row_Number,Rank,Dense_Rank区别

2013-01-23 15:37:36

#### rank() 和dense_rank() 区别

2015-03-30 10:01:54

#### ORACE之RANK()和dense_rank()函数

2014-03-14 22:18:25

#### Oracle-分析函数之排序值rank()和dense_rank()

2016-11-04 20:16:01

#### hive的row_number()、rank()和dense_rank()的区别以及具体使用

2016-10-17 20:05:21

#### Oracle rank和dense_rank排名函数

2013-12-10 23:49:50