MySQL5.7.5及以上版本在进行group by查询时，查询的所有列除了来自于聚合函数（sum、avg、max等）的结果外，其他列都要求在group by字段里，否则会报 [Err] 1055 – Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated.... 类似的错误。这是因为在MySQL5.7之后，sql_mode中ONLY_FULL_GROUP_BY模式默认设置为打开状态，此模式要求分组查询时的列除聚合函数外必须包含在group by字段里。对此MySQL也提供了any_value()函数来抑制ONLY_FULL_GROUP_BY值被拒绝。

1、ONLY_FULL_GROUP_BY模式介绍

从 MySQL 5.7.5 开始，默认 SQL 模式包括 ONLY_FULL_GROUP_BY（在 5.7.5 之前，MySQL 不检测功能依赖，ONLY_FULL_GROUP_BY默认不启用。）ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义，简单的说来，在此模式下，target list中的值要么是来自于聚合函数（sum、avg、max等）的结果，要么是来自于group by list中的表达式的值。

MySQL 5.7.5 及更高版本实现了功能依赖检测。如果SQL模式启用了ONLY_FULL_GROUP_BY（默认情况下），则 MySQL 拒绝在列表、HAVING条件或 ORDER BY列表中引用非聚合列的查询，这些列既不在GROUP BY子句中命名，也不在功能上依赖于它们。
如果ONLY_FULL_GROUP_BY禁用，则标准 SQL 使用的 MySQL 扩展 GROUP BY允许选择列表、 HAVING条件或ORDER BY列表引用非聚合列，即使这些列在功能上不依赖于GROUP BY列。

针对上述情况，若我们查询的值不包含在GROUP BY中，可以使用ANY_VALUE()函数，该函数的作用是返回一个不确定的 expr 值。你可以把它作为一个聚合函数或分析函数来使用。

使用 ANY_VALUE 优化包含 GROUP BY 子句的查询。ANY_VALUE 返回一个组中表达式的值。它被优化为返回第一个值。
它确保不会对任何输入行进行比较，也消除了将每一列指定为 GROUP BY 子句的一部分的必要性。因为 ANY_VALUE 不比较值，所以它在 GROUP BY 查询中比 MIN 或 MAX 更快地返回一个值。

2、ANY_VALUE示例讲解

有一个地区表，需要查询所有的省份，具体数据如下：

mysql> select * from area;
+----+----------------+----------------+-----------+-----------+
| id | proviance_code | proviance_name | city_code | city_name |
+----+----------------+----------------+-----------+-----------+
|  1 |              1 | 河南           |         1 | 商丘      |
|  2 |              1 | 河南           |         2 | 南阳      |
|  3 |              1 | 河南           |         3 | 开封      |
|  7 |              2 | 杭州           |         1 | 余杭区    |
|  8 |              2 | 杭州           |         2 | 拱墅区    |
|  9 |              2 | 杭州           |         3 | 下城区    |
+----+----------------+----------------+-----------+-----------+
6 rows in set (0.00 sec)

针对这个问题，我们有多种解决方案。

2.1 方案一：使用DISTINCT函数

SELECT DISTINCT(proviance_code),proviance_name from area;

2.2 方案二：使用group by函数结合any_value函数

1. mysql5.7.5及以后的版本

在mysql5.7.5及以后的版本中，因为SQL 模式默认开启了 ONLY_FULL_GROUP_BY，我们可以使用 group by 函数结合 any_value 函数的方式。即没有参与分组或排序的字段, 使用ANY_VALUE()函数包括。

any_value：MySQL提供了any_value()函数来抑制ONLY_FULL_GROUP_BY值被拒绝，any_value()会选择被分到同一组的数据里第一条数据的指定列值作为返回数据。

SELECT proviance_code, any_value(proviance_name) from area GROUP BY proviance_code;

2. mysql5.7以及之前的版本

mysql5.7以及之前的版本中还可以使用以下sql:

SELECT proviance_code, proviance_name from area GROUP BY proviance_code;

注意：该语句在mysql5.7版本之后会报错，具体错误如下：
SELECT proviance_code,proviance_name from area GROUP BY proviance_code
1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'area.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
时间: 0.016s
发生错误的原因是proviance_name是非聚合列。

这是因为在MySQL5.7之后，sql_mode中ONLY_FULL_GROUP_BY模式默认设置为打开状态。ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义，简单的说来，在此模式下，target list中的值要么是来自于聚合函数（sum、avg、max等）的结果，要么是来自于group by list中的表达式的值。

若想在mysql5.7版本之后使用上述语句，可以通过修改sql_mode的方式来解决。即关闭ONLY_FULL_GROUP_BY模式。

3、GROUP BY和ANY_VALUE

ANY_VALUE函数会返回一个不确定的 expr 值，你可以把它作为一个聚合函数或分析函数来使用，优化包含 GROUP BY 子句的查询。ANY_VALUE函数确保不会对任何输入行进行比较，也消除了将每一列指定为 GROUP BY 子句的一部分的必要性。因为 ANY_VALUE 不比较值，所以它在 GROUP BY 查询中比 MIN 或 MAX 更快地返回一个值。

1. 使用ANY_VALUE的语句

SELECT c.cust_id , ANY_VALUE ( cust_last_name ), SUM ( amount_sold )

  FROM customers c , sales s

  WHERE s.cust_id = c.cust_id

  GROUP BY c.cust_id ;

2. 不使用ANY_VALUE的语句

上述语句若没有使用 ANY_VALUE 时，则SQL 语句必须要将每一列指定为 GROUP BY 子句，即需要将上例中的 cust_last_name 也指定到 GROUP BY 子句，示例如下：

SELECT c.cust_id , cust_last_name , SUM ( amount_sold )

  FROM customers c , sales s

  WHERE s.cust_id = c.cust_id

  GROUP BY c.cust_id , cust_last_name ;

4、GROUP BY拓展

MySQL 5.7.5 及更高版本允许在启用 ONLY_FULL_GROUP_BY 模式时，GROUP BY时未在子句中命名的非聚合列 ONLY_FULL_GROUP_BY，前提是该列限制为单个值，如下例所示：

mysql> CREATE TABLE mytable (
    ->    id INT UNSIGNED NOT NULL PRIMARY KEY,
    ->    a VARCHAR(10),
    ->    b INT
    -> );

mysql> INSERT INTO mytable
    -> VALUES (1, 'abc', 1000),
    ->        (2, 'abc', 2000),
    ->        (3, 'def', 4000);

mysql> SET SESSION sql_mode = sys.list_add(@@session.sql_mode, 'ONLY_FULL_GROUP_BY');

mysql> SELECT a, SUM(b) FROM mytable WHERE a = 'abc';
+------+--------+
| a    | SUM(b) |
+------+--------+
| abc  |   3000 |
+------+--------+

5、总结

MySQL5.7之后，sql_mode中ONLY_FULL_GROUP_BY模式默认设置为打开状态。
ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义，简单的说来，在此模式下，target list中的值要么是来自于聚合函数（sum、avg、max等）的结果，要么是来自于group by list中的表达式的值
MySQL提供了any_value()函数来抑制ONLY_FULL_GROUP_BY值被拒绝
any_value()会选择被分到同一组的数据里第一条数据的指定列值作为返回数据

目录CONTENT

MySql中group by分组查询的最佳搭档ANY_VALUE函数