MySQL使用GROUP BY分组后，获取每组中时间记录最新的行对应的其他字段

问题描述

在编写MySQL数据库查询语句时，经常遇到使用GROUP BY分组后，需要获取每组中时间记录最新的行对应的其他字段这一情况。例如下表（t_score）：

CREATE TABLE t_score
(
  id INT PRIMARY KEY AUTO_INCREMENT
, student VARCHAR(20)
, course VARCHAR(20)
, score DECIMAL(20, 1)
, examdate DATE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
DEFAULT CHARSET=utf8;

INSERT INTO t_score (student, course, score, examdate) VALUES ('小张', '语文', 89, '2023-06-29');
INSERT INTO t_score (student, course, score, examdate) VALUES ('小张', '数学', 90, '2023-06-29');
INSERT INTO t_score (student, course, score, examdate) VALUES ('小张', '语文', 91, '2024-01-10');
INSERT INTO t_score (student, course, score, examdate) VALUES ('小张', '数学', 93, '2024-01-10');
INSERT INTO t_score (student, course, score, examdate) VALUES ('小李', '语文', 89, '2023-06-29');
INSERT INTO t_score (student, course, score, examdate) VALUES ('小李', '数学', 87, '2023-06-29');
INSERT INTO t_score (student, course, score, examdate) VALUES ('小李', '语文', 90, '2024-01-10');
INSERT INTO t_score (student, course, score, examdate) VALUES ('小李', '数学', 92, '2024-01-10');

如果想要查询每个同学最新一次的数学成绩，以下语句无法查询到想要的数据。

SELECT s.id, s.student, s.course, s.score, MAX(s.examdate)
FROM t_score s
WHERE s.course = '数学'
GROUP BY s.student

查询结果是2023年6月29日小张和小李的成绩。

先对数据按时间倒序排列，再查询仍然不行。经了解是GROUP BY语句导致ORDER BY语句失效。

SELECT t.id, t.student, t.course, t.score, MAX(t.examdate) 
FROM (
	SELECT *
	FROM t_score s
	WHERE s.course = '数学'
	ORDER BY s.examdate DESC
) t
GROUP BY t.student

解决方法一

对数据排序后加LIMIT语句即可使排序生效，从而得到正确结果。

SELECT t.id, t.student, t.course, t.score, MAX(t.examdate) 
FROM (
	SELECT *
	FROM t_score s
	WHERE s.course = '数学'
	ORDER BY s.examdate DESC LIMIT 10000
) t
GROUP BY t.student

解决方法二

对数据排序时加GROUP BY id语句。

SELECT t.id, t.student, t.course, t.score, MAX(t.examdate) 
FROM (
	SELECT *
	FROM t_score s
	WHERE s.course = '数学'
	GROUP BY s.id
	ORDER BY s.examdate DESC
) t
GROUP BY t.student

解决方法三

先查询每组最新时间，再通过时间等字段联合原表查询。

SELECT a.*
FROM t_score a
INNER JOIN (
	SELECT s.student, s.course, MAX(s.examdate) examdate
	FROM t_score s 
	WHERE s.course = '数学'
	GROUP BY s.student
) b ON b.student = a.student AND b.course = a.course AND b.examdate = a.examdate

解决方法四

通过GROUP_CONCAT拼接时间与id字段，然后分解出id。

SELECT *
FROM t_score t
WHERE t.id IN 
(
	SELECT SUBSTRING_INDEX(GROUP_CONCAT(s.id ORDER BY s.examdate DESC, id DESC), ',', 1) id
	FROM t_score s
	WHERE s.course = '数学'
	GROUP BY s.student
)

也可以每个字段都通过GROUP_CONCAT拼接方式取值。

SELECT SUBSTRING_INDEX(GROUP_CONCAT(s.id ORDER BY s.examdate DESC), ',', 1) id
, s.student, s.course
, SUBSTRING_INDEX(GROUP_CONCAT(s.score ORDER BY s.examdate DESC), ',', 1) score
, MAX(s.examdate)
FROM t_score s
WHERE s.course = '数学'
GROUP BY s.student

也可以利用CONCAT_WS函数。

SELECT SUBSTRING_INDEX(MAX(CONCAT_WS(',', s.examdate, s.id)), ',', -1) id
, s.student, s.course
, SUBSTRING_INDEX(MAX(CONCAT_WS(',', s.examdate, s.score)), ',', -1) score
, MAX(s.examdate)
FROM t_score s
WHERE s.course = '数学'
GROUP BY s.student

参考文献

https://www.jb51.net/database/2949790kf.htm
https://blog.csdn.net/qq_39522120/article/details/108617552