一个有关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;

若要永久避免这个问题： 

在mysql> 命令行中执行以下步骤：

1、show variables like "sql_mode";

2、set sql_mode='';
3、set sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES';

 

附带（可以看看）：https://www.cnblogs.com/Vera-y/p/10923999.html