MySQL ANY_VALUE() 函数
ANY_VALUE()
函数对于启用了ONLY_FULL_GROUP_BY
模式,使用GROUP BY
进行查询时很有用;该函数用于抑制启用ONLY_FULL_GROUP_BY
模式时导致的值拒绝;
官方例子:
SELECT name, address, MAX(age) 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
发生错误的原因是address是非聚合列;
使用ANY_VALUE()
解决:
SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;