一、排名问题的三种分类
在mysql中会经常遇到排名问题,大致可分为三类:
1. 同分不同名:即使分数相同,但是排名还是依次按顺序来往后排名。
姓名 | 分数 | 排名 |
张三 | 100 | 1 |
李四 | 90 | 2 |
王五 | 90 | 3 |
赵六 | 80 | 4 |
2.同分同名不跳级:
分数相同,但是同分的时候排名相同。
姓名 | 分数 | 排名 |
张三 | 100 | 1 |
李四 | 90 | 2 |
王五 | 90 | 2 |
赵六 | 80 | 3 |
3.同分同名有跳级:分数相同,虽然同分的时候排名相同,但是会跳级。
姓名 | 分数 | 排名 |
张三 | 100 | 1 |
李四 | 90 | 2 |
王五 | 90 | 2 |
赵六 | 80 | 4 |
二、准备测试数据
准备mysql表:salary_rank表,记载着工人和薪水信息。
SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;DROP TABLE IF EXISTS `salary_rank`;CREATE TABLE `salary_rank` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键', `name` varchar(63) NOT NULL COMMENT '用户名', `salary` decimal(11,2) NOT NULL COMMENT '总分', `period` varchar(63) NOT NULL COMMENT '统计周期', `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='薪水排行榜';
2. 准备salary_rank表的测试数据。
BEGIN;INSERT INTO `salary_rank` VALUES (1, '工人A', 8000.00, '2020-11', '2020-11-27 12:10:22', '2020-11-27 12:10:22');INSERT INTO `salary_rank` VALUES (2, '工人B', 5000.00, '2020-11', '2020-11-27 12:10:22', '2020-11-27 12:10:44');INSERT INTO `salary_rank` VALUES (3, '工人C', 4000.00, '2020-11', '2020-11-27 12:10:22', '2020-11-27 12:10:44');INSERT INTO `salary_rank` VALUES (4, '工人D', 9000.00, '2020-11', '2020-11-27 12:10:22', '2020-11-27 12:10:44');INSERT INTO `salary_rank` VALUES (5, '工人E', 8000.00, '2020-11', '2020-11-27 12:10:22', '2020-11-27 12:10:44');INSERT INTO `salary_rank` VALUES (6, '工人F', 7000.00, '2020-11', '2020-11-27 12:10:22', '2020-11-27 12:10:44');INSERT INTO `salary_rank` VALUES (7, '工人G', 6000.00, '2020-11', '2020-11-27 12:10:22', '2020-11-27 12:10:44');COMMIT;
准备了7条数据进行测试,其中工人A和工人E薪水一样,都是8000元。
三、开始解题
(一) 同薪不同名:工人A和工人E虽然薪水一样,但是排名不同。
1. mysql 8的窗口函数row_number() over()的解法:
SELECT name, salary, row_number() over ( PARTITION BY period ORDER BY salary DESC ) AS rank_num FROM salary_rank;
2. mysql临时变量解法:
SELECT name, salary, (@num := @num + 1 ) `rank` FROM salary_rank, ( SELECT @num := 0 ) temp ORDER BY salary DESC;
(二)同薪同名不跳级:工人A和工人E薪水一样,排名也一样,后续排名也接着往后递增。
mysql 8的窗口函数dense_rank() over()的解法:
SELECT name, salary, dense_rank() over ( PARTITION BY period ORDER BY salary DESC ) AS rank_num FROM salary_rank;
2. mysql临时变量解法:
SELECT t.name, t.salary, t.rank FROM ( SELECT name, salary, @num := @num + IF (@preScore = salary, 0, 1 ) `rank`, @preScore := salary FROM salary_rank, ( SELECT @num := 0, @preScore := 0 ) temp ORDER BY salary DESC ) t;
(三)同薪同名有跳级:工人A和工人E薪水一样,排名也一样,但后续排名不是接着往后递增,而是跳跃至其实际的排名。
1. mysql 8的窗口函数rank() over()的解法:
SELECT name, salary, rank() over ( PARTITION BY period ORDER BY salary DESC ) AS rank_num FROM salary_rank;
2. mysql临时变量解法:
SELECT t.name, t.salary, t.rank FROM ( SELECT name, salary, @num := IF ( @preScore = salary, @num, @total ) `rank`, @preScore := salary, @total := @total + 1 FROM salary_rank, ( SELECT @num := 1, @total := 1, @preScore := 0 ) temp ORDER BY salary DESC ) t;