1.问题
在使用 group by 查询时,sql 语法报错:
SQL_ERROR_INFO: "Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'a.device_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"
翻译:
SELECT list 不在 GROUP BY 子句中,并且包含未聚合列“a.device_id’在功能上不依赖于 GROUP BY 子句中的列;这与 sql_mode=only_full_group_by 不兼容
MySQL 从 5.7.5 版本开始,默认 SQL 模式包括 ONLY_FULL_GROUP_BY(在 5.7.5 之前,MySQL 不检测函数依赖,并且默认不启用 ONLY_FULL_GROUP_BY)。也就是说咱们在写 SQL 语句有用到 group by 聚合函数时,查询的字段一定要被 group by 所包含。
形如:SELECT A, B FROM tb_xxx GROUP BY A;
默认会报 SQL 语法错误,因为字段 B 不在 GROUP BY 聚合条件内,当某个 A 对应多个 B 时,MYSQL 引擎不知道该返回哪个 B(对 A group by 后只能返回一个 B,以前默认返回第一个出现的 B)。
修改为:SELECT A, B FROM tb_xxx GROUP BY A, B;后
可以通过语法检查。但是可能不符合我们的需求,此时应该思考是否真的需要查询字段 B(分组后只会展示第一条 B)。
查询sql_mode
SQL:select @@global.sql_mode;
结果:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
2.解决方案
修改sql语句
添加查询字段 B 到 GROUP BY 后,或者去除查询字段 B。
修改sql_mode
一次性修改:
在运行语句时,每次执行的时候前面都加上一下这句SQL,去修改一下 sql_mode
SET@@sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";
持久性修改(重启mysql会失效):
SET global sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";
永久性修改:
找到MySQL的安装目录下,打开 my.cnf 文件(windows系统是 my.ini 文件),新增
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
修改后,重启MySQL服务。
3.实验
不分组:
分组后只会显示每组第一条数据:
问题背景:
学校下有学生,学生会回答问题。要求统计每个学校的所有学生平均回答问题数量。
A --> B --> C,一对多对多关系。
- device_id:学生id(唯一标识)
- cnt_avg:所有学生平均回答问题数量
group by增加device_id维度:
结合题目要求,最终的结果应该不需要查询 device_id 字段。
参考:
https://blog.csdn.net/weixin_44273248/article/details/125894349