(SELECT A,B FROM T1
union
SELECT A,B FROM T2
union
SELECT A,B FROM T3)
intersect
(select T1.A,T1.B from T1,T2 WHERE T1.A=T2.A AND T1.B=T2.B)
union
SELECT A,B FROM T2
union
SELECT A,B FROM T3)
intersect
(select T1.A,T1.B from T1,T2 WHERE T1.A=T2.A AND T1.B=T2.B)
---------- ----------
1 1
2 1
3 3
4 4SQL> select * from t2;A B
---------- ----------
1 1
2 1
4 4
6 6SQL> select * from t3;A B
---------- ----------
2 1
4 4
6 6
9 9SQL> (
2 (SELECT A,B FROM T1 WHERE (A,B) NOT IN
3 (SELECT T1.A,T1.B FROM T1,T2,T3 WHERE (T1.A=T2.A AND T1.B=T2.B) OR (T1.A=T3.A AND T1.B=T3.B)
)
4 )
5 UNION
6 (SELECT A,B FROM T2 WHERE (A,B) NOT IN
7 (SELECT T2.A,T2.B FROM T2,T3 WHERE T2.A=T3.A AND T2.B=T3.B)
8 )
9 UNION
10 (SELECT A,B from T3) );A B
---------- ----------
1 1
2 1
3 3
4 4
6 6
9 9已选择6行。SQL> select * from t1
2 union
3 select * from t2
4 union
5 select * from t3;A B
---------- ----------
1 1
2 1
3 3
4 4
6 6
9 9已选择6行。
FROM t1
WHERE (a, b) NOT IN (SELECT t1.a, t1.b
FROM t1, t2, t3
WHERE (t1.a = t2.a AND t1.b = t2.b) OR (t1.a = t3.a AND t1.b = t3.b))
UNION
SELECT a, b
FROM t2
WHERE (a, b) NOT IN (SELECT t2.a, t2.b
FROM t2, t3
WHERE t2.a = t3.a AND t2.b = t3.b)
UNION
SELECT a, b
FROM t3;
-------------------------------------------------------
在t1但是不在t2或者t3中 + 在t2中但是不在t3中 + t3
不就是t1+t2+t3吗?
select a, b from t1
union
select a, b from t2
union
select a, b from t3;
在t1但是不在t2或者t3中 + 在t2中但是不在t3中 + t3 不等价于 T1+T2+T3
而应等价于 (T1+T2+T3)-(T1∩T2)+(T1∩t2∩t3)
所以我的语句也要修改为:
(SELECT A,B FROM T1
union
SELECT A,B FROM T2
union
SELECT A,B FROM T3)
intersect
(select T1.A,T1.B from T1,T2 WHERE T1.A=T2.A AND T1.B=T2.B)
union
(select T1.A,T1.B from T1,T2,T3 WHERE T1.A=T2.A AND T1.B=T2.B AND T1.A=T3.A AND T1.B=T3.B)
UNION
SELECT A, B FROM T2
UNION
SELECT A, B FROM T3