文章目录
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
