日期:2014-05-17  浏览次数:20936 次

如何查询同一个表内主键之间一对一的数据
举个例子:
字段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的效率差不多。

支持一下!