MySQL 5.7的ONLY_FULL_GROUP_BY模式

环境准备

CREATE TABLE test(id INT, name VARCHAR(20), age INT, dept INT);
INSERT test VALUES(1,'zhangsan',33,101);
INSERT test VALUES(2,'lisi',34,101);
INSERT test VALUES(3,'wangwu',34,102);
INSERT test VALUES(4,'zhaoliu',34,102);
INSERT test VALUES(5,'tianqi',36,102);

查询每个部门年龄最大员工的姓名，部门，年纪：

mysql> SELECT name, dept, max(age) FROM test GROUP BY dept;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.test.name' 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。

SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

方案二：建议的方案，修改sql语句兼容其它数据库。

SELECT t1.name, t1.dept, t1.age FROM test t1 INNER JOIN (SELECT dept, max(age) maxage FROM test GROUP BY dept) t2 ON t1.dept=t2.dept AND t1.age=t2.maxage;

参考

https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html