柚子快报邀请码778899分享：MySQL中多种排名实现
不同版本MySQL排名实现，利用排名函数，利用自定义变量，不重复连续、并列连续、并列不连续
一、数据库表结构以及数据
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;
SELECT
student_name,
score,
@cur_rank := @cur_rank + 1 AS ranking
FROM
forlan_score
ORDER BY
score DESC;
使用自定义变量（内部sql）（推荐）
SELECT
fs.student_name,
fs.score,
( @cur_rank := @cur_rank + 1 ) AS ranking
FROM
forlan_score fs,
( SELECT @cur_rank := 0 ) r
ORDER BY
score DESC;
2）mysql8实现
ROW_NUMBER()
SELECT
student_name,
score,
ROW_NUMBER() OVER ( ORDER BY score DESC ) AS ranking
FROM
forlan_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
SELECT
fs.student_name,
fs.score,
IF( @pre_score = fs.score, @cur_rank, @cur_rank := @cur_rank + 1 ) AS ranking,
@pre_score := fs.score
FROM
forlan_score fs,( SELECT @cur_rank := 0, @pre_score := NULL ) r
ORDER BY
fs.score DESC;
使用自定义变量 + CASE WHEN
SELECT
fs.student_name,
fs.score,
(
CASE
WHEN @pre_score = fs.score THEN @cur_rank
WHEN @pre_score := fs.score THEN @cur_rank := @cur_rank + 1
END
) AS ranking
FROM
forlan_score fs,(SELECT @cur_rank := 0,@pre_score := NULL) r
ORDER BY
fs.score DESC;
2）mysql8实现
DENSE_RANK()
SELECT
student_name,
score,
DENSE_RANK() OVER ( ORDER BY score DESC ) AS ranking
FROM
forlan_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
SELECT
fs.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
FROM
forlan_score fs,
(SELECT @cur_rank := 0,@pre_score := NULL,@row_num := 0 ) r
ORDER BY
fs.score DESC;
使用自定义变量 + CASE WHEN
SELECT
fs.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
FROM
forlan_score fs,
( SELECT @cur_rank := 0, @pre_score := NULL, @row_num := 0 ) r
ORDER BY
fs.score DESC;
2）mysql8实现
RANK()
SELECT
student_name,
score,
RANK() OVER ( ORDER BY score DESC ) AS ranking
FROM
forlan_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
SELECT
fs.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_name
FROM
forlan_score fs,
( SELECT @cur_rank := 0, @cur_couse := NULL ) r
ORDER BY
fs.course_name,fs.score DESC;
2）mysql8实现
ROW_NUMBER()
SELECT
student_name,
course_name,
score,
ROW_NUMBER() OVER (PARTITION BY course_name ORDER BY course_name,score DESC) AS ranking
FROM
forlan_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
SELECT
fs.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_name
FROM
forlan_score fs,
( SELECT @cur_rank := 0, @pre_score := NULL, @cur_couse := NULL ) r
ORDER BY
fs.course_name,fs.score DESC;
2）mysql8实现
DENSE_RANK()
SELECT
student_name,
course_name,
score,
DENSE_RANK() OVER (PARTITION BY course_name ORDER BY course_name,score DESC) AS ranking
FROM
forlan_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
SELECT
fs.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_name
FROM
forlan_score fs,
( SELECT @cur_rank := 0, @pre_score := NULL, @row_num := 0,@cur_couse := NULL ) r
ORDER BY
fs.course_name,fs.score DESC;
2）mysql8实现
RANK()
SELECT
student_name,
course_name,
score,
RANK() OVER (PARTITION BY course_name ORDER BY course_name,score DESC) AS ranking
FROM
forlan_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 |
+--------------+-------------+-------+---------+
柚子快报邀请码778899分享：MySQL中多种排名实现
发表评论