SQL查找具有多个字段的重复项（无唯一ID）解决方法

我正在尝试使用以下几个字段从数据库中查找重复的供应商vendor表和供应商地址桌子。问题是，我进行的内部联接越多，查询丢失潜在结果的次数就越少。虽然我的供应商 ID 中没有重复项，但我希望找到类似的潜在供应商 ID。

这是我到目前为止的查询：

SELECT 
     o.vendor_id
    ,o.vndr_name_shrt_user
    ,O.COUNTRY 
    ,O.VENDOR_NAME_SHORT 
    ,B.POSTAL
    ,B.ADDRESS1
    ,SAME_ADDRESS_NB
    ,SAME_POSTAL_NB
    ,OC.SAME_SHORT_NAME
    ,oc.SAME_USER_NUM
FROM VENDOR o

JOIN vendor_addr B ON o.VENDOR_ID = B.VENDOR_ID

INNER JOIN (
    SELECT vndr_name_shrt_user, COUNT(*) AS SAME_USER_NUM
    FROM VENDOR 
    WHERE COUNTRY = 'CANADA'
    AND VENDOR_STATUS = 'A'
    GROUP BY vndr_name_shrt_user
    HAVING COUNT(*) > 1
) oc on o.vndr_name_shrt_user = oc.vndr_name_shrt_user

INNER JOIN ( SELECT VENDOR_NAME_SHORT, COUNT(*) AS SAME_SHORT_NAME
    FROM VENDOR 
    WHERE COUNTRY = 'CANADA'
    AND VENDOR_STATUS = 'A'
    GROUP BY VENDOR_NAME_SHORT
    HAVING COUNT(*) > 1
) oc on o.VENDOR_NAME_SHORT = oc.VENDOR_NAME_SHORT

INNER JOIN (SELECT POSTAL, COUNT(*) AS SAME_POSTAL_NB
    FROM vendor_addr 
    WHERE COUNTRY = 'CANADA'
    AND COUNTRY ='CANADA'
    AND POSTAL != ' '
    GROUP BY POSTAL
    HAVING COUNT(*) > 1
) oc on b.POSTAL = oc.POSTAL

INNER JOIN (SELECT ADDRESS1, COUNT(*) AS SAME_ADDRESS_NB
    FROM ps_vendor_addr 
    WHERE COUNTRY = 'CANADA'
    AND COUNTRY ='CANADA'
    AND ADDRESS1 != ' '
    GROUP BY ADDRESS1
    HAVING COUNT(*) > 1
) oc on b.ADDRESS1 = oc.ADDRESS1   
WHERE O.COUNTRY ='CANADA' 
    AND B.COUNTY = 'CANADA';

让我们获得一些有趣的数据，这些数据在不同的属性上具有链接的重复项：

CREATE TABLE data ( ID, A, B, C ) AS
  SELECT 1, 1, 1, 1 FROM DUAL UNION ALL -- Related to #2 on column A
  SELECT 2, 1, 2, 2 FROM DUAL UNION ALL -- Related to #1 on column A, #3 on B & C, #5 on C
  SELECT 3, 2, 2, 2 FROM DUAL UNION ALL -- Related to #2 on columns B & C, #5 on C
  SELECT 4, 3, 3, 3 FROM DUAL UNION ALL -- Related to #5 on column A
  SELECT 5, 3, 4, 2 FROM DUAL UNION ALL -- Related to #2 and #3 on column C, #4 on A
  SELECT 6, 5, 5, 5 FROM DUAL;          -- Unrelated

现在，我们可以使用分析函数获得一些关系（没有任何连接）：

SELECT d.*,
       LEAST(
         FIRST_VALUE( id ) OVER ( PARTITION BY a ORDER BY id ),
         FIRST_VALUE( id ) OVER ( PARTITION BY b ORDER BY id ),
         FIRST_VALUE( id ) OVER ( PARTITION BY c ORDER BY id )
       ) AS duplicate_of
FROM   data d;

这使：

ID A B C DUPLICATE_OF
-- - - - ------------
 1 1 1 1            1
 2 1 2 2            1
 3 2 2 2            2
 4 3 3 3            4
 5 3 4 2            2
 6 5 5 5            6

但这并没有说明#4与#5相关，#5与#2相关，然后与#1相关......

这可以通过分层查询找到：

SELECT id, a, b, c,
       CONNECT_BY_ROOT( id ) AS duplicate_of
FROM   data
CONNECT BY NOCYCLE ( PRIOR a = a OR PRIOR b = b OR PRIOR c = c );

但这会产生很多很多重复的行（因为它不知道从哪里开始层次结构，所以会依次选择每一行作为根） - 相反，您可以使用第一个查询为层次结构查询提供一个起点，当ID and DUPLICATE_OF值是相同的：

SELECT id, a, b, c,
       CONNECT_BY_ROOT( id ) AS duplicate_of
FROM   (
  SELECT d.*,
         LEAST(
           FIRST_VALUE( id ) OVER ( PARTITION BY a ORDER BY id ),
           FIRST_VALUE( id ) OVER ( PARTITION BY b ORDER BY id ),
           FIRST_VALUE( id ) OVER ( PARTITION BY c ORDER BY id )
         ) AS duplicate_of
  FROM   data d
)
START WITH id = duplicate_of
CONNECT BY NOCYCLE ( PRIOR a = a OR PRIOR b = b OR PRIOR c = c );

这使：

ID A B C DUPLICATE_OF
-- - - - ------------
 1 1 1 1            1
 2 1 2 2            1
 3 2 2 2            1
 4 3 3 3            1
 5 3 4 2            1
 1 1 1 1            4
 2 1 2 2            4
 3 2 2 2            4
 4 3 3 3            4
 5 3 4 2            4
 6 5 5 5            6

由于搜索中出现 #4 的局部最小值，仍然有一些行是重复的...可以通过简单的方法将其删除GROUP BY:

SELECT id, a, b, c,
       MIN( duplicate_of ) AS duplicate_of
FROM   (
  SELECT id, a, b, c,
         CONNECT_BY_ROOT( id ) AS duplicate_of
  FROM   (
    SELECT d.*,
           LEAST(
             FIRST_VALUE( id ) OVER ( PARTITION BY a ORDER BY id ),
             FIRST_VALUE( id ) OVER ( PARTITION BY b ORDER BY id ),
             FIRST_VALUE( id ) OVER ( PARTITION BY c ORDER BY id )
           ) AS duplicate_of
    FROM   data d
  )
  START WITH id = duplicate_of
  CONNECT BY NOCYCLE ( PRIOR a = a OR PRIOR b = b OR PRIOR c = c )
)
GROUP BY id, a, b, c;

给出输出：

ID A B C DUPLICATE_OF
-- - - - ------------
 1 1 1 1            1
 2 1 2 2            1
 3 2 2 2            1
 4 3 3 3            1
 5 3 4 2            1
 6 5 5 5            6