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

发布时间：2020-08-25作者：laosun阅读（3630）

最近博主被朋友问到了一道题目，该题目应该是不少公司的面试题，发布文章记录一下。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记录

 发表评论

 请文明留言

  发表

共 0 条评论