mysql 5.7 full_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;

參考