一个有关group by的错误

事例：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT department_name,MIN(salary),departments.manager_id
FROM departments,employees
WHERE departments.department_id=employees.department_id
AND commission_pct IS NOT NULL
GROUP BY department_name;

运行结果出现：

[Err] 1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘myemployees.departments.manager_id‘ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

大意为：表达式departments.manager_id不在group by子句中，并且包含非聚合列‘myemployees.departments.manager_id’,该功能不依赖与group by 子句中的列，这与sql_mode=only_full_group_by不兼容

原因：

这个错误发生咋MySQL5.7.5和以后上，因为5.7.5默认的sql模式配置为ONLY_FULL_GROUP_BY，这个配置启动的为‘严格ANSIsql规则’，此规则严格要求在group by的时候，没有聚合的列（聚合的列表示group by必须带着所有要查询的列）

 代码更正：

SELECT department_name,MIN(salary),departments.manager_id
FROM departments,employees
WHERE departments.department_id=employees.department_id
AND commission_pct IS NOT NULL
GROUP BY department_name,departments.manager_id;

一个有关group by的错误

原文：https://www.cnblogs.com/Vera-y/p/10923850.html