MySQL GROUP BY 分组后,按照某一列进行排序「所有学生分数表,查询每个学生最高的分数记录」

发布时间:2020-08-25作者:laosun阅读(209)

MySQL

    最近博主被朋友问到了一道题目,该题目应该是不少公司的面试题,发布文章记录一下。mysql group by后如何排序的问题。

    group by 和 order by 一起使用时,会先使用group by 分组,并取出分组后的第一条数据,所以后面的order by 排序时根据取出来的第一条数据来排序的,但是第一条数据不一定是分组里面的最大数据。 也即是说group by 后 order by 是不生效的。

    看下数据结构:

    CREATE TABLE `t_student`  (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
      `scope` int(11) NULL DEFAULT NULL,
      `add_time` timestamp(0) NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

    插入一批数据:

    INSERT INTO `t_student`(`id`, `name`, `scope`, `add_time`) VALUES (1, '老孙', 90, '2020-08-22 16:19:16');
    INSERT INTO `t_student`(`id`, `name`, `scope`, `add_time`) VALUES (2, '老孙', 90, '2020-08-23 16:19:25');
    INSERT INTO `t_student`(`id`, `name`, `scope`, `add_time`) VALUES (3, '老孙', 99, '2020-08-13 16:19:36');
    INSERT INTO `t_student`(`id`, `name`, `scope`, `add_time`) VALUES (4, '老孙', 95, '2020-08-15 16:19:45');
    INSERT INTO `t_student`(`id`, `name`, `scope`, `add_time`) VALUES (5, 'AAA', 97, '2020-08-12 16:19:54');
    INSERT INTO `t_student`(`id`, `name`, `scope`, `add_time`) VALUES (6, 'AAA', 95, '2020-08-20 16:20:03');
    INSERT INTO `t_student`(`id`, `name`, `scope`, `add_time`) VALUES (7, 'AAA', 96, '2020-08-10 16:20:11');
    INSERT INTO `t_student`(`id`, `name`, `scope`, `add_time`) VALUES (8, '老孙', 99, '2020-08-12 16:21:13');

    image.png


    现在需求是取出每个学生分数最高(时间最晚)的一条记录?


    从图中我们可以看到,老孙这个学生,最高分为99分,但是有两条记录,时间最晚的是ID为3的这条记录。AAA这个用户最高分为97分,ID为5。

    首先博主想到的就是使用max来进行获取,但是发现该表结构不足以支持,因为获取到的最高分max(scope)并不是唯一的。如下所示:

    SELECT
    	s1.* 
    FROM
    	t_student s1,
    	( SELECT NAME, MAX( scope ) AS scope FROM t_student GROUP BY NAME ) tmp 
    WHERE
    	s1.`name` = tmp.`name` 
    	AND s1.scope = tmp.scope 
    ORDER BY
    	s1.add_time DESC;

    所以想到了另外一种方式,先order by,后group by,如下所示:

    select tmp.* from (select * from t_student ORDER BY scope desc, add_time desc limit 100000) tmp  GROUP BY tmp.`name`;

    就能实现该题目所描述的情况。

    现在有人有疑问了,为什么要使用limit ,大家可以使用EXPLAIN看下执行计划就行,使用了limit后,执行计划中会有一条derived记录

    image.png


2 +1

版权声明

 数据库  mysql  sql

 请文明留言

0 条评论