举个例子：
字段1，字段2，字段3
A A A
A B A
B B A
B E A
C D A
C D B
这样的数据我想查出字段1和字段2之间一对一的数据
字段1，字段2
C D
怎么来组织SQL语句啊？
我自己写了个，但感觉效率不高
怎么能优化一下，或者更好的方法
select distinct t1,t2
from table1
where t1 in (select t1
from (select distinct t1,t2 from table1) a
group by t1
having count(1)=1)
and t2 in (select t2
from (select distinct t1,t2 from table1) a
group by t2
having count(1)=1)
------解决方案--------------------
- SQL code
-- 下面是不是你想要的结果? select distinct t1, t2 from (select t.*, count(1) over(partition by t1, t2) v_count from test t) where v_count > 1
------解决方案--------------------
可否？
- SQL code
SQL> SELECT C1, C2 2 FROM (SELECT C1, 3 C2, 4 COUNT(C2) OVER(PARTITION BY C1) R1, 5 COUNT(C1) OVER(PARTITION BY C2) R2 6 FROM (SELECT DISTINCT C1, C2 FROM T)) 7 WHERE R1 = 1 8 AND R2 = 1;C1 C2-- --C DD F
------解决方案--------------------
- SQL code
-- TRY IT ..SQL> SELECT * FROM TEST_NUM3;FIELD1 FIELD2 FIELD3 FIELD4------ ------ ------ ------A A A AA C B BB B C CE B D DC D E EC D F FD F G GD F G HD F G ZH I J M10 rows selectedSQL> SELECT FIELD1, FIELD2, FIELD3, FIELD4 2 FROM (SELECT TT.*, 3 COUNT(1) OVER(PARTITION BY FIELD1) NEW_FIELD1, 4 COUNT(1) OVER(PARTITION BY FIELD2) NEW_FIELD2, 5 COUNT(1) OVER(PARTITION BY FIELD3) NEW_FIELD3, 6 COUNT(1) OVER(PARTITION BY FIELD1, FIELD2, FIELD3) COUNTS 7 FROM TEST_NUM3 TT) ZZ 8 WHERE NEW_FIELD1 = NEW_FIELD2 9 AND NEW_FIELD2 = NEW_FIELD3 10 AND NEW_FIELD3 = COUNTS;FIELD1 FIELD2 FIELD3 FIELD4------ ------ ------ ------D F G GD F G HD F G ZH I J M
------解决方案--------------------
select a.*
from t1 a,
(select ia.seg1 from t1 ia group by ia.seg1 having count(*)=1) b,
(select ib.seg1 from t1 ib group by ib.seg1,ib.seg2 having count(*)>1) c
where a.seg1=b.seg1 or a.seg1=c.seg1;
这个也可以实现，只是不知效率如何，楼主试后可否给个评价？
写成in（列表）的格式
select a.*
from t1 a
where a.seg1 in (select ia.seg1 from t1 ia group by ia.seg1 having count(*)=1) or
a.seg1 in (select ib.seg1 from t1 ib group by ib.seg1,ib.seg2 having count(*)>1)
我感觉这两个SQL的效率差不多。
支持一下！