Mysql分组时报错

场景

SELECT sc.sid,AVG(sc.score) AS avg_score, stu.sname
FROM sc
LEFT JOIN student stu ON stu.sid = sc.sid
GROUP BY sc.sid
HAVING AVG(sc.score) >= 60

上方sql语句报错
SELECT list is not in GROUP BY clause and contains nonaggregated column 'exercise_50.stu.Sname' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

解释：
由于你的数据库中设置了sql_mode=only_full_group_by，导致在GROUP BY子句中需要包含所有非聚合列，否则会报错。

上方查询中，列stu.sname不在GROUP BY子句中，也不是聚合函数，因此报错。

解决方法

SELECT sc.sid, AVG(sc.score) AS avg_score, stu.sname
FROM sc
JOIN student stu ON stu.sid = sc.sid
GROUP BY sc.sid, stu.sname
HAVING AVG(sc.score) >= 60;

将学生姓名 stu.sname 添加到了GROUP BY子句中，以满足only_full_group_by模式的要求。