mysql 排名_mysql经典排名问题的六种解法

一、排名问题的三种分类

在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

二、准备测试数据

1. 准备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薪水一样，排名也一样，后续排名也接着往后递增。

1. 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;