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

V客 2021-04-07

1066

一、排名问题的三种分类

在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;

mysql

文章转载自V客，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

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

评论