如何查询同一个表内主键之间一对一的数据

Question

举个例子：
字段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)

Answer 1

-- 下面是不是你想要的结果?
 select distinct t1, t2
   from (select t.*, count(1) over(partition by t1, t2) v_count from test t)
  where v_count > 1

Answer 2

-- 如果只是字段1对字段2是一对多的关系，可以用下面的SQL查出来：
SQL> SELECT FIELD1, MAX(FIELD2) NEW_FIELD2
  2    FROM TABLE_NAME TT
 14   GROUP BY FIELD1
 15  HAVING COUNT(DISTINCT FIELD2) = 1;FIELD1 NEW_FIELD2
------ ----------
C      D

Answer 3

与一楼的方法差不多 不过这样好理解  select t1,t2  from table  group by t1,t2 having count(*)=1 t1 和 t2 的一一对应就出来了
如果想都查出来  再套一层
      

Answer 4

-- sleepzzzzz 这个好像不行，要改一下哦：
-- 下面是我加了一个E,F,C记录的结果
SQL> SELECT * FROM TEST_NUM2;FIELD1 FIELD2 FIELD3
------ ------ ------
A      A      A
A      B      A
B      B      A
B      E      A
C      D      A
C      D      B
E      F      C7 rows selectedSQL> SELECT DISTINCT FIELD1, FIELD2
  2    FROM (SELECT T.*, COUNT(1) OVER(PARTITION BY FIELD1, FIELD2) V_COUNT
  3            FROM TEST_NUM2 T)
  4   WHERE V_COUNT > 1;FIELD1 FIELD2
------ ------
C      D-- 所以改成下面的就好了：
SQL> SELECT DISTINCT FIELD1, FIELD2
  2    FROM (SELECT T.*, COUNT(DISTINCT FIELD2) OVER(PARTITION BY FIELD1) V_COUNT
  3            FROM TEST_NUM2 T)
  4   WHERE V_COUNT = 1;FIELD1 FIELD2
------ ------
C      D
E      F

Answer 5

不好意思是我举的例子数据不太好，让你误解了一点
字段1，字段2，字段3
  A      A      A
  A      C      B   ------- 字段1中的值A 与字段2中的值A,B 各组成一条记录，这样就相当于一对二 
  B      B      C
  E      B      D   ------- 字段2中的值B 与字段2中的值B,E 各组成一条记录，这样就相当于一对二 
  C      D      E
  C      D      F   ------- 字段1中的值C 与且只与字段2中的值D 组成记录，虽然两条，但也算一对一
  D      F      G   ------- 很明显算一对一
这样的数据我想查出字段1和字段2之间一对一的数据
字段1，字段2
  C      D 
  D      F

Answer 6

-- 2 WAYS:
SQL> SELECT * FROM TEST_NUM2;FIELD1 FIELD2 FIELD3
------ ------ ------
A      A      A
A      B      A
B      B      A
B      E      A
C      D      A
C      D      B
E      F      C7 rows selectedSQL> SELECT FIELD1, MAX(FIELD2) NEW_FIELD2
  2    FROM TEST_NUM2 TT
  3   GROUP BY FIELD1
  4  HAVING COUNT(DISTINCT FIELD2) = 1;FIELD1 NEW_FIELD2
------ ----------
C      D
E      FSQL> SELECT DISTINCT FIELD1, FIELD2
  2    FROM (SELECT T.*, COUNT(DISTINCT FIELD2) OVER(PARTITION BY FIELD1) V_COUNT
  3            FROM TEST_NUM2 T)
  4   WHERE V_COUNT = 1;FIELD1 FIELD2
------ ------
C      D
E      F

Answer 7

COUNT(DISTINCT FIELD2)
这种语句是Oracle独有的么？我在DB2和MSSQL上执行不支持
我需要的运行环境是DB2，不过那边板块人少，就再也便也发了一个，呵呵
其实我需要的是三个主键之间的1:1:1的关系的数据，因为比较复杂，所以先考虑一下两个字段

Answer 8

可否？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

Answer 9

如果字段2对字段1也在在一对多的关系，那么就用楼上的SQL。因为不知道ORACLE中的语法在DB2和MSSQL哪一些是支持的，所以是能在ORACLE中调试。

Answer 10

恩，这个是好用的,非常感谢。适当增加点难度吧，呵呵
字段1，字段2，字段3，字段4
  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
三个字段的1：1：1关系，并且主要的我还想把字段4也查出来
现在看合格的数据只有
 D      F      G      G

Answer 11

-- 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      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      G
D      F      G      H
D      F      G      Z
H      I      J      M

Answer 12

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的效率差不多。支持一下！

Answer 13

多加个条件就OK了啊SQL> SELECT *
  2    FROM T
  3   WHERE (C1, C2, C3) IN (SELECT C1, C2, C3
  4                            FROM (SELECT C1,
  5                                         C2,
  6                                         C3,
  7                                         COUNT(C1) OVER(PARTITION BY C1) R1,
  8                                         COUNT(C2) OVER(PARTITION BY C2) R2,
  9                                         COUNT(C3) OVER(PARTITION BY C3) R3
 10                                    FROM (SELECT DISTINCT C1, C2, C3 FROM T))
 11                           WHERE R1 = 1
 12                             AND R2 = 1
 13                             AND R3 = 1);C1 C2 C3 C4
-- -- -- --
D  F  G  G