[简记]MySQL下GroupBy与Max结合获取分组最大的正确用法
环境
mysql version : 8
示例表: score
create table score (
id int not null primary key auto_increment comment '主键',
name varchar(32) not null comment '名字',
score_date date not null comment '考试时间',
score int not null comment '分数'
) comment '分数表';
数据准备
- 执行数据新增语句
INSERT INTO test.score (id, name, score_time, score) VALUES (1, 'Alice', '2021-01-25', 90);
INSERT INTO test.score (id, name, score_time, score) VALUES (2, 'Alice', '2021-01-24', 90);
INSERT INTO test.score (id, name, score_time, score) VALUES (3, 'Alice', '2021-01-22', 50);
INSERT INTO test.score (id, name, score_time, score) VALUES (4, 'Alice', '2021-01-21', 30);
INSERT INTO test.score (id, name, score_time, score) VALUES (5, 'Bob', '2021-01-21', 90);
INSERT INTO test.score (id, name, score_time, score) VALUES (6, 'Alice', '2021-01-25', 50);
INSERT INTO test.score (id, name, score_time, score) VALUES (7, 'Bob', '2021-01-22', 90);
INSERT INTO test.score (id, name, score_time, score) VALUES (8, 'Bob', '2021-01-25', 30);
INSERT INTO test.score (id, name, score_time, score) VALUES (9, 'Bob', '2021-01-24', 90);
- 得到Alice和Bob每天考试分数如下
+----+-------+------------+-------+
| id | name | score_time | score |
+----+-------+------------+-------+
| 1 | Alice | 2021-01-25 | 90 |
| 2 | Alice | 2021-01-24 | 90 |
| 3 | Alice | 2021-01-22 | 50 |
| 4 | Alice | 2021-01-21 | 30 |
| 5 | Bob | 2021-01-21 | 90 |
| 6 | Alice | 2021-01-25 | 50 |
| 7 | Bob | 2021-01-22 | 90 |
| 8 | Bob | 2021-01-25 | 30 |
| 9 | Bob | 2021-01-24 | 90 |
+----+-------+------------+-------+
需求描述
使用SQL查询出表T中以A列为分组的B列最大值情况下的C列最大下的所有行。
如使用sql查询出Alice和Bob分数最高的最新的一天的记录。对于这条描述应当是:在 _分数表(T:score)_ 中以 名字(A:name) 分组, 查询出 分数(B:score) 最高的 时间最近(C:score_time) 的记录，结果应当是:
+----+-------+------------+-------+
| id | name | score_time | score |
+----+-------+------------+-------+
| 1 | Alice | 2021-01-25 | 90 |
| 5 | Bob | 2021-01-24 | 90 |
+----+-------+------------+-------+
一般我们查询的需求是一次group by 与 一次 max,比如查询出Alice和Bob最大的分数时间记录，但这次有点不一样的不就是时间记录还要是最近的时间记录，就是说时间也要是符合要求中的最大的。
验证
用法1(错误):
select id, name, max(score), max(score_time) from score group by name
这个用法sql是错误的，无法执行。这个错误发生在mysql 5.7 版本及以上版本会出现的问题，提示以下错误
sql_mode=only_full_group_by
两种处理方式，一个是修改sql_model，第二个是修改被选择的列
select name, max(score), max(score_time) from score group by name
结果如下:
+----+-------+------------+-------+
| id | name | score_time | score |
+----+-------+------------+-------+
| 1 | Alice | 2021-01-25 | 90 |
| 5 | Bob | 2021-01-21 | 90 |
+----+-------+------------+-------+
用法2(正确):
两层group by与max的嵌套，也就是第一个group by和max找出符合第一个条件的数据，第二个group by与max找出基于第一条件的情况的符合第二条件的数据
select score.name, max(score.score_time), score.score from score left join (
select name, max(score) as score from score group by name
) as high_score on high_score.name = score.name and high_score.score = score.score where high_score.name is not null group by score.name, score.score;
结果，正确:
+-------+-----------------------+-------+
| name | max(score.score_time) | score |
+-------+-----------------------+-------+
| Alice | 2021-01-25 | 90 |
| Bob | 2021-01-24 | 90 |
+-------+-----------------------+-------+
效率:
+-------+-----------------------+---------+
| 数据量 | 索引 | 时间/ms |
+-------+-----------------------+---------+
| 1w | 无 | 90 |
| 1w | 组合索引:name,score | 20 |
+-------+-----------------------+---------+