MySQL聚合函数问题 [英] MySQL aggregate function problem

问题描述

在下面的示例中，为什么min()查询返回结果，但max()查询没有返回结果?

In the following example, why does the min() query return results, but the max() query does not?

mysql> create table t(id int, a int);
Query OK, 0 rows affected (0.10 sec)

mysql> insert into t(id, a) values(1, 1);
Query OK, 1 row affected (0.03 sec)

mysql> insert into t(id, a) values(1, 2);
Query OK, 1 row affected (0.02 sec)

mysql> select * from t
    -> ;
+------+------+
| id   | a    |
+------+------+
|    1 |    1 |
|    1 |    2 |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from t where a < 4;
+------+------+
| id   | a    |
+------+------+
|    1 |    1 |
|    1 |    2 |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from t where a < 4 having a = max(a);
Empty set (0.00 sec)

mysql> select * from t where a < 4 having a = min(a);
+------+------+
| id   | a    |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)

推荐答案

HAVING子句用于过滤行组.您引用min(a)和max(a)(在没有任何GROUP BY子句的情况下)它们汇总表中的所有a值，但随后将其与单个a值进行比较.

The HAVING clause is used to filter groups of rows. You reference min(a) and max(a) which (in the absence of any GROUP BY clause) aggregate over all a values in the table but then use a comparison against a single a value.

那么MySQL应该使用哪个a值?我知道的所有其他RDBMS都会在此时抛出错误，但是MySQL确实允许这样做. 来自文档

So which a value is MySQL supposed to use? All other RDBMSs that I know of would throw an error at this point however MySQL does allow this. From the docs

标准SQL不允许HAVING子句命名任何列 在GROUP BY子句中找不到，除非将其包含在汇总中 功能. MySQL允许使用此类列来简化 计算.此扩展假定未分组的列将 具有相同的分组值. 否则，结果是 不确定.

Standard SQL does not permit the HAVING clause to name any column not found in the GROUP BY clause unless it is enclosed in an aggregate function. MySQL permits the use of such columns to simplify calculations. This extension assumes that the nongrouped columns will have the same group-wise values. Otherwise, the result is indeterminate.

因此，根据您的结果，您似乎会发现它最终使用1作为a的标量值，但是不能保证此行为，并且同样可以使用2或其他任何方法现有的a值.

So in your case from the results you are getting it appears that it ended up using 1 as the scalar value for a but this behaviour is not guaranteed and it could equally well have used 2 or any other existing a value.

这篇关于MySQL聚合函数问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！