开发者

MySQL aggregate function problem

开发者 https://www.devze.com 2023-04-02 12:19 出处:网络
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);

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 havin开发者_StackOverflow社区g 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)


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.

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

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.

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.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号