MySQL ANY_VALUE() 函数


ANY_VALUE()函数对于启用了ONLY_FULL_GROUP_BY模式,使用GROUP BY进行查询时很有用;该函数用于抑制启用ONLY_FULL_GROUP_BY模式时导致的值拒绝;

官方例子:

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

如需转载,请注明出处;本文地址:https://www.perfcode.com/p/mysql-any-value-function.html