SELECT name, address FROM t GROUP BY name;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP
BY clause and contains nonaggregated column 'mydb.t.address' which
is not functionally dependent on columns in GROUP BY clause; this
is incompatible with sql_mode=only_full_group_by
错误原因
mysql数据库在only_full_group_by模式下,select中含有的非函数字段名未完全包含在group by 子句中,就会报1055错误,如本例中address字段不在group by后,就会报1055错误。
解决方法
1.在 group by 后将select中含有的非函数字段名补充完。
SELECT name, address FROM t GROUP BY name,address;
2.使用ANY_VALUE()函数引用address
SELECT name, ANY_VALUE(address)FROM t GROUP BY name;
3.修改address为表t的主键或者将address改为t表中唯一的非空列
4.禁用only_full_group_by