CREATE TABLE t1 (a int, b int);
CREATE TABLE t2 (c int, d int);
CREATE TABLE t3 (e int);
INSERT INTO t1 VALUES
(1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40);
INSERT INTO t2 VALUES
(2,10), (2,20), (4,10), (5,10), (3,20), (2,40);
INSERT INTO t3 VALUES (10), (30), (10), (20) ;
二 PostgreSQL 版本
test=# select version();
version
-------------------------------------------------------------
PostgreSQL 9.3.4, compiled by Visual C++ build 1700, 64-bit
(1 row)
三 执行查询(按SQL的语义,应该得到的结果是a列值为1和2,没有3和4)
test=# SELECT t1.a FROM t1
test-# GROUP BY t1.a
test-# HAVING t1.a < ALL(
test(# SELECT t2.c FROM t2
test(# GROUP BY t2.c
test(# HAVING EXISTS(
test(# SELECT t3.e FROM t3
test(# GROUP BY t3.e
test(# HAVING SUM(t1.a+t2.c) < t3.e/4));
a
---
4
1
3
2
(4 rows)
四 查看查询执行计划
test=# EXPLAIN
test-# SELECT t1.a FROM t1
test-# GROUP BY t1.a
test-# HAVING t1.a < ALL(
test(# SELECT t2.c FROM t2
test(# GROUP BY t2.c
test(# HAVING EXISTS(
test(# SELECT t3.e FROM t3
test(# GROUP BY t3.e
test(# HAVING SUM(t1.a+t2.c) < t3.e/4));
QUERY PLAN
---------------------------------------------------------------------------
HashAggregate (cost=36.75..969979.25 rows=200 width=4)
Filter: (SubPlan 2)
-> Seq Scan on t1 (cost=0.00..31.40 rows=2140 width=4)
SubPlan 2
-> HashAggregate (cost=47.45..9651.45 rows=200 width=4)
Filter: (SubPlan 1)
-> Seq Scan on t2 (cost=0.00..31.40 rows=2140 width=4)
SubPlan 1
-> HashAggregate (cost=48.00..48.67 rows=67 width=4)
-> Seq Scan on t3 (cost=0.00..46.00 rows=800 width=4)
Filter: (sum((t1.a + t2.c)) < (e / 4))
(11 rows)
五 MySQL的执行结果
mysql> SELECT t1.a FROM t1
-> GROUP BY t1.a
-> HAVING t1.a < ALL(
-> SELECT t2.c FROM t2
-> GROUP BY t2.c
-> HAVING EXISTS(
-> SELECT t3.e FROM t3
-> GROUP BY t3.e
-> HAVING SUM(t1.a+t2.c) < t3.e/4));
+------+
| a |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.01 sec)
================
附记: 有朋友联系我说:
PostgreSQL查询结果错误一例 HAVING SUM(t1.a+t2.c) < t3.e/4)); postgres 和mysql在 除法操作 / 处理不一样, 如果都是int,pg会截断;而mysql不会; select 10/4; 和 select 10/4.0 结果不一样
这话确实对. 所以进一步解释如下(PostgreSQL 1到3例, 4例是MySQL):
(1)把 t3.e/4变为 t3.e/4.0,这样,后者比前者更大一些(更容易使得条件满足),但是得到的结果是:a为1和2
test=# SELECT t1.a FROM t1
test-# GROUP BY t1.a
test-# HAVING t1.a < ALL(
test(# SELECT t2.c FROM t2
test(# GROUP BY t2.c
test(# HAVING EXISTS(
test(# SELECT t3.e FROM t3
test(# GROUP BY t3.e
test(# HAVING SUM(t1.a+t2.c) < t3.e/4.0));
a
---
1
2
(2 rows)
(2) 而 SUM(t1.a+t2.c) < t3.e/4< t3.e/4.0 , 结果得到a列值为4,1,3,2; 这显然不对(对比上例).
test=# SELECT t1.a FROM t1
test-# GROUP BY t1.a
test-# HAVING t1.a < ALL(
test(# SELECT t2.c FROM t2
test(# GROUP BY t2.c
test(# HAVING EXISTS(
test(# SELECT t3.e FROM t3
test(# GROUP BY t3.e
test(# HAVING SUM(t1.a+t2.c) < t3.e/4));
a
---
4
1
3
2
(4 rows)
(3) 验证 t3.e/4, t3.e/4.0的大小关系
test=# select t3.e/4, t3.e/4.0 from t3;
?column? | ?column?
----------+--------------------
2 | 2.5000000000000000
7 | 7.5000000000000000
2 | 2.5000000000000000
5 | 5.0000000000000000
(4 rows)
(4) MySQL一例
mysql> select 10/4, 10/4.0;
+--------+--------+
| 10/4 | 10/4.0 |
+--------+--------+
| 2.5000 | 2.5000 |
+--------+--------+
1 row in set (0.00 sec)