文章目录

• 一、数据库表结构以及数据
• 二、实现排名（不分组）
• • 1、不重复，连续
  • • 1.1、不同版本实现
    • • 1）mysql5.7实现
      • 2）mysql8实现
    • 1.2、效果
  • 2、并列排名，连续
  • • 2.1、不同版本实现
    • • 1）mysql5.7实现
      • 2）mysql8实现
    • 2.2、效果
  • 3、并列排名，不连续
  • • 3.1、不同版本实现
    • • 1）mysql5.7实现
      • 2）mysql8实现
    • 3.2、效果
• 三、按照课程分组实现排名
• • 1、不重复，连续
  • • 1.1、不同版本实现
    • • 1）mysql5.7实现
      • 2）mysql8实现
    • 1.2、效果
  • 2、并列排名，连续
  • • 2.1、不同版本实现
    • • 1）mysql5.7实现
      • 2）mysql8实现
    • 2.2、效果
  • 3、并列排名，不连续
  • • 3.1、不同版本实现
    • • 1）mysql5.7实现
      • 2）mysql8实现
    • 3.2、效果

一、数据库表结构以及数据

CREATE TABLE `forlan_score` (  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',  `student_name` varchar(255) DEFAULT NULL COMMENT '学生名称',  `score` int(20) DEFAULT '-1' COMMENT '分数',`course_name` varchar(255) DEFAULT NULL COMMENT '课程',  PRIMARY KEY (`id`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='学生成绩表';INSERT INTO `test`.`forlan_score` (`id`, `student_name`, `score`, `course_name`) VALUES (1, '小明', 70, '数学');INSERT INTO `test`.`forlan_score` (`id`, `student_name`, `score`, `course_name`) VALUES (2, '小红', 65, '英语');INSERT INTO `test`.`forlan_score` (`id`, `student_name`, `score`, `course_name`) VALUES (3, '小林', 100, '数学');INSERT INTO `test`.`forlan_score` (`id`, `student_name`, `score`, `course_name`) VALUES (4, '小黄', 100, '语文');INSERT INTO `test`.`forlan_score` (`id`, `student_name`, `score`, `course_name`) VALUES (5, '小东', 80, '语文');INSERT INTO `test`.`forlan_score` (`id`, `student_name`, `score`, `course_name`) VALUES (6, '小美', 90, '英语');INSERT INTO `test`.`forlan_score` (`id`, `student_name`, `score`, `course_name`) VALUES (7, '小伟', 88, '英语');INSERT INTO `test`.`forlan_score` (`id`, `student_name`, `score`, `course_name`) VALUES (8, '小小', 100, '数学');

二、实现排名（不分组）

1、不重复，连续

1.1、不同版本实现

1）Mysql5.7实现

• 使用自定义变量（外部sql）

SET @cur_rank := 0;SELECTstudent_name,score,@cur_rank := @cur_rank + 1 AS ranking FROMforlan_score ORDER BYscore DESC;

• 使用自定义变量（内部sql）（推荐）

SELECTfs.student_name,fs.score,( @cur_rank := @cur_rank + 1 ) AS ranking FROMforlan_score fs,( SELECT @cur_rank := 0 ) r ORDER BYscore DESC;

2）mysql8实现

ROW_NUMBER()

SELECTstudent_name,score,ROW_NUMBER() OVER ( ORDER BY score DESC ) AS ranking FROMforlan_score;

1.2、效果

+--------------+-------+---------+| student_name | score | ranking |+--------------+-------+---------+| 小林         |   100 |       1 || 小黄         |   100 |       2 || 小小         |   100 |       3 || 小美         |    90 |       4 || 小伟         |    88 |       5 || 小东         |    80 |       6 || 小明         |    70 |       7 || 小红         |    65 |       8 |+--------------+-------+---------+

2、并列排名，连续

2.1、不同版本实现

1）mysql5.7实现

• 使用自定义变量 + IF

SELECTfs.student_name,fs.score,IF( @pre_score = fs.score, @cur_rank, @cur_rank := @cur_rank + 1 ) AS ranking,@pre_score := fs.score FROMforlan_score fs,( SELECT @cur_rank := 0, @pre_score := NULL ) r ORDER BYfs.score DESC;

• 使用自定义变量 + CASE WHEN

SELECTfs.student_name,fs.score,(CASEWHEN @pre_score = fs.score THEN @cur_rank WHEN @pre_score := fs.score THEN @cur_rank := @cur_rank + 1 END ) AS ranking FROMforlan_score fs,(SELECT @cur_rank := 0,@pre_score := NULL) r ORDER BYfs.score DESC;

2）mysql8实现

DENSE_RANK()

SELECTstudent_name,score,DENSE_RANK() OVER ( ORDER BY score DESC ) AS ranking FROMforlan_score;

2.2、效果

+--------------+-------+---------+| student_name | score | ranking |+--------------+-------+---------+| 小林         |   100 |       1 || 小黄         |   100 |       1 || 小小         |   100 |       1 || 小美         |    90 |       2 || 小伟         |    88 |       3 || 小东         |    80 |       4 || 小明         |    70 |       5 || 小红         |    65 |       6 |+--------------+-------+---------+

3、并列排名，不连续

3.1、不同版本实现

1）mysql5.7实现

• 使用自定义变量 + IF

SELECTfs.student_name,fs.score,@row_num := @row_num + 1,IF( @pre_score = fs.score, @cur_rank, @cur_rank := @row_num ) AS ranking,@pre_score := fs.score FROMforlan_score fs,(SELECT @cur_rank := 0,@pre_score := NULL,@row_num := 0 ) r ORDER BYfs.score DESC;

• 使用自定义变量 + CASE WHEN

SELECTfs.student_name,fs.score,@row_num := @row_num + 1,( CASE WHEN @pre_score = fs.score THEN @cur_rank WHEN @pre_score := fs.score THEN @cur_rank := @row_num END ) AS ranking FROMforlan_score fs,( SELECT @cur_rank := 0, @pre_score := NULL, @row_num := 0 ) r ORDER BYfs.score DESC;

2）mysql8实现

RANK()

SELECTstudent_name,score,RANK() OVER ( ORDER BY score DESC ) AS ranking FROMforlan_score;

3.2、效果

+--------------+-------+---------+| student_name | score | ranking |+--------------+-------+---------+| 小林         |   100 |       1 || 小黄         |   100 |       1 || 小小         |   100 |       1 || 小美         |    90 |       4 || 小伟         |    88 |       5 || 小东         |    80 |       6 || 小明         |    70 |       7 || 小红         |    65 |       8 |+--------------+-------+---------+

三、按照课程分组实现排名

1、不重复，连续

1.1、不同版本实现

1）mysql5.7实现

• 使用自定义变量 + IF

SELECTfs.student_name,fs.course_name,fs.score,IF(@cur_couse = course_name, @cur_rank := @cur_rank+1, @cur_rank :=1) AS ranking,@cur_couse := fs.course_nameFROMforlan_score fs,( SELECT @cur_rank := 0,  @cur_couse := NULL ) r ORDER BYfs.course_name,fs.score DESC;

2）mysql8实现

ROW_NUMBER()

SELECTstudent_name,course_name,score,ROW_NUMBER() OVER (PARTITioN BY course_name ORDER BY course_name,score DESC) AS rankingFROMforlan_score;

1.2、效果

+--------------+-------------+-------+---------+| student_name | course_name | score | ranking |+--------------+-------------+-------+---------+| 小林         | 数学        |   100 |       1 || 小小         | 数学        |   100 |       2 || 小明         | 数学        |    70 |       3 || 小美         | 英语        |    90 |       1 || 小伟         | 英语        |    88 |       2 || 小红         | 英语        |    65 |       3 || 小黄         | 语文        |   100 |       1 || 小东         | 语文        |    80 |       2 |+--------------+-------------+-------+---------+

2、并列排名，连续

2.1、不同版本实现

1）mysql5.7实现

• 使用自定义变量 + IF

SELECTfs.student_name,fs.course_name,fs.score,IF(@cur_couse = course_name, IF( @pre_score = fs.score, @cur_rank, @cur_rank := @cur_rank+1 ), @cur_rank :=1) AS ranking,@pre_score := fs.score,@cur_couse := fs.course_nameFROMforlan_score fs,( SELECT @cur_rank := 0, @pre_score := NULL, @cur_couse := NULL ) r ORDER BYfs.course_name,fs.score DESC;

2）mysql8实现

DENSE_RANK()

SELECTstudent_name,course_name,score,DENSE_RANK() OVER (PARTITION BY course_name ORDER BY course_name,score DESC) AS rankingFROMforlan_score;

2.2、效果

+--------------+-------------+-------+---------+| student_name | course_name | score | ranking |+--------------+-------------+-------+---------+| 小林         | 数学        |   100 |       1 || 小小         | 数学        |   100 |       1 || 小明         | 数学        |    70 |       2 || 小美         | 英语        |    90 |       1 || 小伟         | 英语        |    88 |       2 || 小红         | 英语        |    65 |       3 || 小黄         | 语文        |   100 |       1 || 小东         | 语文        |    80 |       2 |+--------------+-------------+-------+---------+

3、并列排名，不连续

3.1、不同版本实现

1）mysql5.7实现

• 使用自定义变量 + IF

SELECTfs.student_name,fs.course_name,fs.score,IF(@cur_couse = course_name, @row_num := @row_num + 1, @row_num :=1),IF(@cur_couse = course_name, IF( @pre_score = fs.score, @cur_rank, @cur_rank := @row_num ),@cur_rank :=1) AS ranking,@pre_score := fs.score,@cur_couse := fs.course_nameFROMforlan_score fs,( SELECT @cur_rank := 0, @pre_score := NULL, @row_num := 0,@cur_couse := NULL ) r ORDER BYfs.course_name,fs.score DESC;

2）mysql8实现

RANK()

SELECTstudent_name,course_name,score,RANK() OVER (PARTITION BY course_name ORDER BY course_name,score DESC) AS rankingFROMforlan_score;

3.2、效果

+--------------+-------------+-------+---------+| student_name | course_name | score | ranking |+--------------+-------------+-------+---------+| 小林         | 数学        |   100 |       1 || 小小         | 数学        |   100 |       1 || 小明         | 数学        |    70 |       3 || 小美         | 英语        |    90 |       1 || 小伟         | 英语        |    88 |       2 || 小红         | 英语        |    65 |       3 || 小黄         | 语文        |   100 |       1 || 小东         | 语文        |    80 |       2 |+--------------+-------------+-------+---------+

来源地址：https://blog.csdn.net/qq_36433289/article/details/128676858