MYSQL实现排名函数RANK，DENSE_RANK和ROW_NUMBER

╰+攻爆jí腚メ 2022-10-29 04:28 240阅读 0赞

## 1. 排名分类 ##

### 1.1 区别RANK，DENSE\_RANK和ROW\_NUMBER ###

RANK并列跳跃排名，并列即相同的值，相同的值保留重复名次，遇到下一个不同值时，跳跃到总共的排名。

DENSE\_RANK并列连续排序，并列即相同的值，相同的值保留重复名次，遇到下一个不同值时，依然按照连续数字排名。

ROW\_NUMBER连续排名，即使相同的值，依旧按照连续数字进行排名。  
区别如图：

![在这里插入图片描述][20210208145006647.png]

### 1.2 分组排名 ###

将数据分组后排名，区别如图：  
![在这里插入图片描述][20210208145101313.png]

## 2. 准备数据 ##

创建一张分数表，里面有字段：分数score，课程号course\_id和学生号student\_id。  
执行如下SQL语句，进行导入数据。

create table score(
      student_id varchar(10),
      course_id varchar(10),
      score decimal(18,1)
    );
    
    insert into score values('01' , '01' , 80);
    insert into score values('01' , '02' , 90);
    insert into score values('01' , '03' , 99);
    insert into score values('02' , '01' , 70);
    insert into score values('02' , '02' , 60);
    insert into score values('02' , '03' , 80);
    insert into score values('03' , '01' , 80);
    insert into score values('03' , '02' , 80);
    insert into score values('03' , '03' , 80);
    insert into score values('04' , '01' , 50);
    insert into score values('04' , '02' , 30);
    insert into score values('04' , '03' , 20);
    insert into score values('05' , '01' , 76);
    insert into score values('05' , '02' , 87);
    insert into score values('06' , '01' , 31);
    insert into score values('06' , '03' , 34);
    insert into score values('07' , '02' , 89);
    insert into score values('07' , '03' , 98);
    insert into score values('08' , '02' , 89);
    insert into score values('09' , '02' , 89);

查看数据：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI1OTMzMjQ5_size_16_color_FFFFFF_t_70]

## 3. 不分组排名 ##

### 3.1 连续排名 ###

使用ROW\_NUMBER实现：

SELECT score,ROW_NUMBER() OVER (ORDER BY score DESC) rankingFROM score;

使用变量实现：

SELECT s.score, (@cur_rank := @cur_rank + 1) rankingFROM score s, (SELECT @cur_rank := 0) rORDER BY score DESC;

结果如图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI1OTMzMjQ5_size_16_color_FFFFFF_t_70 1]

### 3.2 并列跳跃排名 ###

使用RANK实现：

SELECT course_id, score,RANK() OVER(ORDER BY score DESC)FROM score

使用变量和IF语句实现：

SELECT s.score,
    @rank_counter := @rank_counter + 1,
    IF(@pre_score = s.score, @cur_rank, @cur_rank := @rank_counter) ranking,
    @pre_score := s.score
    FROM score s, (SELECT @cur_rank :=0, @pre_score := NULL, @rank_counter := 0) r
    ORDER BY s.score DESC

使用变量和CASE语句实现：

SELECT s.score,
    @rank_counter := @rank_counter + 1,
    (
      CASE
      WHEN @pre_score = s.score THEN @cur_rank
      WHEN @pre_score := s.score THEN @cur_rank := @rank_counter
      END
    ) ranking
    FROM score s, (SELECT @cur_rank :=0, @pre_score := NULL, @rank_counter := 0) r
    ORDER BY s.score DESC;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI1OTMzMjQ5_size_16_color_FFFFFF_t_70 2]

### 3.3 并列连续排名 ###

使用DENSE\_RANK实现：

SELECT course_id, score,DENSE_RANK() OVER(ORDER BY score DESC) FROM score

使用变量和IF语句实现：

SELECT s.score,
    IF(@pre_score = s.score, @cur_rank, @cur_rank := @cur_rank + 1) ranking,
    @pre_score := s.score
    FROM score s, (SELECT @cur_rank :=0, @pre_score = NULL) r
    ORDER BY s.score DESC;

使用变量和CASE语句实现：

SELECT s.score,
    (
      CASE
      WHEN @pre_score = s.score THEN @cur_rank
      WHEN @pre_score := s.score THEN @cur_rank := @cur_rank + 1
      END
    ) ranking
    FROM score s, (SELECT @cur_rank :=0, @pre_score = NULL) r
    ORDER BY s.score DESC;

结果如图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI1OTMzMjQ5_size_16_color_FFFFFF_t_70 3]

## 4. 分组排名 ##

### 4.1 分组连续排名 ###

使用ROW\_NUMBER实现：

SELECT course_id, score,ROW_NUMBER() OVER (PARTITION BY course_id ORDER BY score DESC) ranking FROM score

使用变量和IF语句实现：

SELECT s.course_id, s.score,
    IF(@pre_course_id = s.course_id, @cur_rank := @cur_rank + 1, @cur_rank := 1) ranking,
    @pre_course_id := s.course_id
    FROM score s, (SELECT @cur_rank := 0, @pre_course_id := NULL) r
    ORDER BY course_id, score DESC;

结果如图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI1OTMzMjQ5_size_16_color_FFFFFF_t_70 4]

### 4.2 分组并列跳跃排名 ###

使用ROW\_NUMBER实现：

SELECT course_id, score,ROW_NUMBER() OVER (PARTITION BY course_id ORDER BY score DESC) ranking FROM score

使用变量和IF语句实现：

结果如图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI1OTMzMjQ5_size_16_color_FFFFFF_t_70 5]

### 4.3 分组并列连续排名 ###

使用DENSE\_RANK实现：

SELECT course_id, score,DENSE_RANK() OVER(PARTITION BY course_id ORDER BY score DESC)FROM score

使用变量和IF语句实现：

SELECT s.course_id, s.score,
    IF(@pre_course_id = s.course_id,
       IF(@pre_score = s.score, @cur_rank, @cur_rank := @cur_rank + 1),
       @cur_rank := 1) ranking,
    @pre_score := s.score,
    @pre_course_id := s.course_id
    FROM score s, (SELECT @cur_rank :=0, @pre_score = NULL, @pre_course_id := NULL) r
    ORDER BY course_id, score DESC;

可以将上述的IF条件提取出来：

SELECT s.course_id, s.score,
    IF(@pre_score = s.score, @cur_rank, @cur_rank := @cur_rank + 1) temp1,
    @pre_score := s.score temp2,
    IF(@pre_course_id = s.course_id, @cur_rank, @cur_rank := 1) ranking,
    @pre_course_id := s.course_id
    FROM score s, (SELECT @cur_rank :=0, @pre_score = NULL, @pre_course_id := NULL) r
    ORDER BY course_id, score DESC;

结果如图：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI1OTMzMjQ5_size_16_color_FFFFFF_t_70 6]

[20210208145006647.png]: /images/20221024/dcd841ea498c47d198f759c3fe6b0b51.png
[20210208145101313.png]: /images/20221024/12e78d9a0c914176bd407f2657773591.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI1OTMzMjQ5_size_16_color_FFFFFF_t_70]: /images/20221024/e4f9c76cbcda443a936d51719c51aeb4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI1OTMzMjQ5_size_16_color_FFFFFF_t_70 1]: /images/20221024/b5d7745c5f774c1b836c957dc28df0f6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI1OTMzMjQ5_size_16_color_FFFFFF_t_70 2]: /images/20221024/a9c72fb57e934beb9f0740d6bc708f37.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI1OTMzMjQ5_size_16_color_FFFFFF_t_70 3]: /images/20221024/085b52fd2f44444ea94f01cc57ca72fa.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI1OTMzMjQ5_size_16_color_FFFFFF_t_70 4]: /images/20221024/1b40bbc9d0894bd3b1542d6e43f4693b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI1OTMzMjQ5_size_16_color_FFFFFF_t_70 5]: /images/20221024/9da2834c9b4a415fa8877ea890adb269.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI1OTMzMjQ5_size_16_color_FFFFFF_t_70 6]: /images/20221024/d2be04d7899c4a118ccb08983769b727.png