select distinct c1 from t1 where c1 not in (select c1 from (select c1,c2 from t1 minus select c1,c2 from t2)) union select distinct c1 from t2 where c1 not in (select c1 from (select c1,c2 from t2 minus select c1,c2 from t1))
fycooer(水影) 的就已经对了阿。
waterfirer(水清) :可否不用in?效率太低
我上面的那个也不对, select distinct c1 from t1,t2 where c1 in (select distinct c1 from t1 where c1 not in (select c1 from (select c1,c2 from t1 minus select c1,c2 from t2))) and c1 in (select distinct c1 from t2 where c1 not in (select c1 from (select c1,c2 from t2 minus select c1,c2 from t1))) 感觉很乱,应该能简化的,回头清醒了整理一下。
没有重复记录时可以这样 select distinct c1 from (select t1.c1,count(*) t from t1,t2 where t1.c1=t2.c1 and t1.c2=t2.c2 group by t1.c1) a, (select c1,count(*) t from (select * from t1 union select * from t2) group by c1) b where a.c1=b.c1 and a.t=b.t
from T1,T2
where T1.C2 = T2.C2;
是这意思么
from T1,T2
where T1.C2 = T2.C2
是這個意思
|C1|C2|
--------
|1 |11|
--------
|1 |12|
--------
|1 |13|
--------
|2 |21|
--------
|2 |22|
--------
|3 |31|
--------T2:
|C1|C2|
--------
|1 |11|
--------
|1 |13|
--------
|2 |21|
--------
|2 |22|
--------
|3 |31|
--------T1中C1=1对应3个C2,T2中C1=1对应2个C2,不完全匹配,不选
T1中C1=2、C1=2对应的C2与T2完全匹配,选诸位清楚了吗?谢谢
union
select distinct c1 from t2 where c1 not in (select c1 from (select c1,c2 from t2 minus select c1,c2 from t1))
select distinct c1 from t1,t2 where c1 in (select distinct c1 from t1 where c1 not in (select c1 from (select c1,c2 from t1 minus select c1,c2 from t2))) and c1 in (select distinct c1 from t2 where c1 not in (select c1 from (select c1,c2 from t2 minus select c1,c2 from t1)))
感觉很乱,应该能简化的,回头清醒了整理一下。
select
distinct c1
from
(select t1.c1,count(*) t from t1,t2 where t1.c1=t2.c1 and t1.c2=t2.c2 group by t1.c1) a,
(select c1,count(*) t from (select * from t1 union select * from t2) group by c1) b
where a.c1=b.c1 and a.t=b.t
如果T1中C1=1对应C2=1和C2=2,T2中C1=1对应C2=1和C2=3,仍然认为不匹配