问题描述
在编写MySQL数据库查询语句时，经常遇到使用GROUP BY分组后，需要获取每组中时间记录最新的行对应的其他字段这一情况。例如下表（t_score）：
| id | student | course | score | examdate |
|---|---|---|---|---|
| 1 | 小张 | 语文 | 89.0 | 2023-06-29 |
| 2 | 小张 | 数学 | 90.0 | 2023-06-29 |
| 3 | 小张 | 语文 | 91.0 | 2024-01-10 |
| 4 | 小张 | 数学 | 93.0 | 2024-01-10 |
| 5 | 小李 | 语文 | 89.0 | 2023-06-29 |
| 6 | 小李 | 数学 | 87.0 | 2023-06-29 |
| 7 | 小李 | 语文 | 90.0 | 2024-01-10 |
| 8 | 小李 | 数学 | 92.0 | 2024-01-10 |
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函数。文章来源：https://www.toymoban.com/news/detail-796884.html
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文章来源地址https://www.toymoban.com/news/detail-796884.html
到了这里，关于MySQL使用GROUP BY分组后，获取每组中时间记录最新的行对应的其他字段的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
