root@localhost : plx 10:26:58> select * from t2;
+------+----+
| a | id |
+------+----+
| 10 | 1 |
| 1 | 3 |
+------+----+
2 rows in set (0.00 sec)
root@localhost : plx 10:27:04> SELECT * FROM t2 GROUP BY NULL;
+------+----+
| a | id |
+------+----+
| 10 | 1 |
+------+----+
1 row in set (0.00 sec)
root@localhost : plx 10:30:21> SELECT MAX(a),MIN(a),MAX(id),MIN(id) FROM t2 GROUP BY NULL;
+--------+--------+---------+---------+
| MAX(a) | MIN(a) | MAX(id) | MIN(id) |
+--------+--------+---------+---------+
| 10 | 1 | 3 | 1 |
+--------+--------+---------+---------+
1 row in set (0.00 sec)
是不是发现问题了?
MAX/MIN函数取值是全局的,而不是LIMIT 1这个分组内的。
因此,当GROUP BY NULL的时候,MAX/MIN函数是取所有数据里的最大和最小值!
所以啊,”SELECT * FROM t HAVING id=MIN(id)”本质上是”SELECT * FROM t HAVING id=1″, 就能返回一条记录,而”SELECT * FROM t HAVING id=MAX(id)”本质上是”SELECT * FROM t HAVING id=3″,当然没有返回记录,这就是问题的根源。
测试一下GROUP BY a,这样就对了,每个分组内只有一行,所以MAX/MIN一样大,这回是取得组内最大和最小值。
root@localhost : plx 11:29:49> SELECT MAX(a),MIN(a),MAX(id),MIN(id) FROM t2 GROUP BY a;
+--------+--------+---------+---------+
| MAX(a) | MIN(a) | MAX(id) | MIN(id) |
+--------+--------+---------+---------+
| 1 | 1 | 3 | 3 |
| 10 | 10 | 5 | 5 |
+--------+--------+---------+---------+
2 rows in set (0.00 sec)
GROUP BY NULL时MAX/MIN的行为,是这个问题的本质
上面这个是一个人的博客摘下来的
个人理解:
分组相当于对表进行拆分,一个表可以通过对某个字段分组,得到几组数据,然后由 having 进行筛选的时候,相当于每一组是一张表,对每一组进行条件筛选,可能不太好理解,看下面的这个例子: