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

悠悠 2021-12-14 23:11 462阅读 0赞

--------------------

### 文章目录 ###

*   *   *  1. 排名分类
         *   *  1.1 区别RANK，DENSE\_RANK和ROW\_NUMBER
             *  1.2 分组排名
         *  2. 准备数据
         *  3. 不分组排名
         *   *  3.1 连续排名
             *  3.2 并列跳跃排名
             *  3.3 并列连续排名
         *  4. 分组排名
         *   *  4.1 分组连续排名
             *  4.2 分组并列跳跃排名
             *  4.3 分组并列连续排名

> 在MYSQL的最新版本MYSQL8已经支持了排名函数`RANK`，`DENSE_RANK`和`ROW_NUMBER`。但是在就得版本中还不支持这些函数，只能自己实现。实现方法主要用到了条件判断语句（`CASE WHEN`或`IF`）和添加临时变量。

--------------------

### 1. 排名分类 ###

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

*  RANK**并列跳跃排名**，并列即相同的值，相同的值保留重复名次，遇到下一个不同值时，跳跃到总共的排名。
 *  DENSE\_RANK**并列连续排序**，并列即相同的值，相同的值保留重复名次，遇到下一个不同值时，依然按照连续数字排名。
 *  ROW\_NUMBER**连续排名**，即使相同的值，依旧按照连续数字进行排名。

区别如图：  
![在这里插入图片描述][20190704010620649.png]

#### 1.2 分组排名 ####

将数据分组后排名，区别如图：  
![在这里插入图片描述][20190704010611368.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);

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

### 3. 不分组排名 ###

#### 3.1 连续排名 ####

1.  使用`ROW_NUMBER`实现：

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

1.  使用`变量`实现：

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

结果如图：  
![在这里插入图片描述][20190704092431863.png]

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

1.  使用`RANK`实现：

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

1.  使用`变量`和`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;

1.  使用`变量`和`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;

结果如图：  
![在这里插入图片描述][20190704092453409.png]

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

1.  使用`DENSE_RANK`实现：

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

1.  使用`变量`和`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;

1.  使用`变量`和`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;

结果如图：  
![在这里插入图片描述][20190704092534901.png]

### 4. 分组排名 ###

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

1.  使用`ROW_NUMBER`实现：

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

1.  使用`变量`和`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;

结果如图：  
![在这里插入图片描述][20190704092649986.png]

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

1.  使用`RANK`实现：

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

1.  使用`变量`和`IF`语句实现：

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

结果如图：  
![在这里插入图片描述][20190704092710363.png]

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

1.  使用`DENSE_RANK`实现：

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

1.  使用`变量`和`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;

结果如图：  
![在这里插入图片描述][20190704092734471.png]

[20190704010620649.png]: /images/20211214/8556693fa12f4219a2f7e0fd049d6543.png
[20190704010611368.png]: /images/20211214/8e362c5f3fb84104a0e30e1287549fce.png
[20190704091855474.png]: /images/20211214/f603b5a40c704931ab2d77a6d228df83.png
[20190704092431863.png]: /images/20211214/3c04ecda6b974b57ba04d202f5b78c9b.png
[20190704092453409.png]: /images/20211214/ef440fb900b1491b8356724373fe2f3e.png
[20190704092534901.png]: /images/20211214/963e15494d2542d4ac3cc32baa9d6cdc.png
[20190704092649986.png]: /images/20211214/953604824f5f4b32adddbb4ecb483ec8.png
[20190704092710363.png]: /images/20211214/918a22d0f8124767a41192eacefb4d59.png
[20190704092734471.png]: /images/20211214/a227ac54f2ce46c88dd59fe563ff1a39.png