sql：Group by配合max出现的问题？

参考博客
SQL-GROUP BY语句在MySQL中的一个错误使用被兼容的情况

数据源

学生表 成绩表

需求

找出每个学生分数最高的科目和成绩

解决方案

1.对学生id 进行group by 配合Max从成绩表中查出每个学生最高的分数

select student_id, max(grade_num) from grade group by student_id;

image.png

2.我想把学生名称也显示出来，这时候就要联合student表进行联合查询了

select s.student_id,
       s.student_name,
       max(g.grade_num)
from grade g
inner join student s on s.student_id= g.student_id
group by g.student_id;

image.png

3.这时候问题还没暴露出来, 我们再来把功课名称展现出来

select s.student_id,
       s.student_name,
       max(g.grade_num),
       g.grade_course
from grade g
inner join student s on s.student_id= g.student_id
group by g.student_id;

image.png

这时候问题就暴露出来了: 成绩表名称是错误的，和原来的成绩表对比下很容易发现张三最高的成绩是数学。

为什么筛选出来的成绩表名称是错误的？

1.其实我们进行第3步查询的时候首先会遇到以下错误
「ERROR 1055 (42000): Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'demo.g.grade_course' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by」
原因解析：由于grade_course 不在 group by 的范围中，所以会报以上错误，可以参考mysql 新版本出现group by 语句不兼容问题博客中的方式暂时解决，但这不是最好的方法，也不满足我们的需求。
2.为什么筛选出来的成绩表名称是错误的？
因为mysql执行group by配合聚合语句比如max时，对于不在group中的字段，会取遇到的第一条数据中该字段对应的数据，在例子中就是语文了，而不是成绩最高的那一条数据中的课程名。

怎么解决成绩表名称错误？

1. sql语句

select b.student_id,b.student_name,a.grade_num,a.grade_course from grade a inner join(
    select s.student_id as student_id,
           s.student_name as student_name,
           max(g.grade_num) as grade_num
    from grade g
    inner join student s on s.student_id= g.student_id
    group by g.student_id) b
on a.student_id = b.student_id
and a.grade_num = b.grade_num; 

image.png

2.sql解析

• 先通过学生表和成绩表进行链接查询出来最高成绩的学生id和最高成绩，形成临时表b
• 然后将成绩表和临时表b连接查询出来每个学生最高成绩的课程和分数

注：遗留问题，因为例子中成绩表的设计问题，如果有学生同一门课成绩相同，上面的sql是有问题的，所以聚合函数出来的值要和其他值能在查询表中形成唯一键才ok。