侧边栏壁纸
博主头像
孔子说JAVA博主等级

成功只是一只沦落在鸡窝里的鹰,成功永远属于自信且有毅力的人!

  • 累计撰写 285 篇文章
  • 累计创建 125 个标签
  • 累计收到 4 条评论

目 录CONTENT

文章目录

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

孔子说JAVA
2022-07-06 / 0 评论 / 0 点赞 / 111 阅读 / 3,927 字 / 正在检测是否收录...

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中的表达式的值。

  1. MySQL 5.7.5 及更高版本实现了功能依赖检测。如果SQL模式启用了ONLY_FULL_GROUP_BY(默认情况下),则 MySQL 拒绝在列表、HAVING条件或 ORDER BY列表中引用非聚合列的查询,这些列既不在GROUP BY子句中命名,也不在功能上依赖于它们。

  2. 如果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、总结

  1. MySQL5.7之后,sql_mode中ONLY_FULL_GROUP_BY模式默认设置为打开状态。

  2. ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义,简单的说来,在此模式下,target list中的值要么是来自于聚合函数(sum、avg、max等)的结果,要么是来自于group by list中的表达式的值

  3. MySQL提供了any_value()函数来抑制ONLY_FULL_GROUP_BY值被拒绝

  4. any_value()会选择被分到同一组的数据里第一条数据的指定列值作为返回数据

0

评论区