发布时间:2020-08-25作者:laosun阅读(3460)
最近博主被朋友问到了一道题目,该题目应该是不少公司的面试题,发布文章记录一下。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');
现在需求是取出每个学生分数最高(时间最晚)的一条记录?
从图中我们可以看到,老孙这个学生,最高分为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记录
版权属于: 技术客
原文地址: https://www.sunjs.com/article/detail/29c233387ab840949dd4de76bb359aad.html
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。