怎么查询同一个表内主键之间一对一的数据

如何查询同一个表内主键之间一对一的数据
举个例子：
字段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  D
D  F

------解决方案--------------------

SQL code

-- TRY IT ..
SQL> SELECT * FROM TEST_NUM3;

FIELD1 FIELD2 FIELD3 FIELD4
------ ------ ------ ------
A      A      A      A
A      C      B      B
B      B      C      C
E      B      D      D
C      D      E      E
C      D      F      F
D      F      G      G
D      F      G      H
D      F      G      Z
H      I      J      M

10 rows selected

SQL> 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      G
D      F      G      H
D      F      G      Z
H      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的效率差不多。

支持一下！

怎么查询同一个表内主键之间一对一的数据

相关推荐