MySQL通过分组聚合获取每组里面最新的一条记录及其附加信息

MySQL分组聚合获取每组里面最新的一条记录

---

背景

现在有一张学生成绩表 t_score，表结构如下：

CREATE TABLE `t_score`
(
	`id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  	`stu_no` varchar(100) NOT NULL COMMENT '学号',
  	`course_no` varchar(100) NOT NULL COMMENT '课程编号',
  	`score` double NOT NULL COMMENT '课程成绩',
  	`create_date` date NOT NULL COMMENT '成绩时间',
  	PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='学生成绩表';

成绩表里面有如下数据：

INSERT INTO `t_score`(`stu_no`, `course_no`, `score`, `create_date`)
VALUES
('s1001', 'c100', 78, '2023-05-10'),
('s1001', 'c100', 88, '2023-07-10'),
('s1001', 'c100', 85, '2023-06-10'),
('s1001', 'c101', 65, '2023-05-10'),
('s1001', 'c101', 70, '2023-06-10'),
('s1001', 'c101', 78, '2023-07-10');

现在有个需求，需要从成绩表里面查询每个学生各门课程最新的成绩时间和对应的成绩。

实现

通过分析，可以了解到，为了查询每个学生各门课程最新的成绩时间，需要先通过 group by 对学生编号和课程编号进行分组聚合，同时对成绩时间取最大值（MAX），sql 如下：

SELECT `stu_no`, `course_no`, MAX(`create_date`) `create_date`
FROM `t_score`
GROUP BY `stu_no`, `course_no`;

结果如下：

s1001	c100	2023-07-10
s1001	c101	2023-07-10

到此为止，我们已经通过 group by 知道了每个学生各门课程最新的成绩时间。但需求还需要获取每门课程的成绩信息，因此，我们可以通过将表 t_score 与上述 group by 的结果进行自关联 join 操作，进而获取每个学生各门课程最近成绩所对应的成绩信息，sql 如下：

SELECT s1.`stu_no`, s1.`course_no`, s1.`create_date`, s1.`score`
FROM `t_score` s1
INNER JOIN (
	SELECT `stu_no`, `course_no`, MAX(`create_date`) `create_date`
	FROM `t_score`
	GROUP BY `stu_no`, `course_no`
) s2 ON s1.`stu_no` = s2.`stu_no` AND s1.`course_no` = s2.`course_no` AND s1.`create_date` = s2.`create_date`;

结果如下：

s1001	c100	2023-07-10	88
s1001	c101	2023-07-10	78

总结

好的，以上就是MySQL通过分组聚合获取每组里面最新的一条记录的简单实现。上述实现的思路其实就是先获取最新的成绩时间，接着再通过时间找到对应的成绩信息。