查找具有相同信息的不同列上的重复项

Question

我已尝试使用Google搜索，但没有运气。我可以找到的所有答案都是通过列中的不同信息。在这种情况下，我有多个列中的相同信息（电话号码）。

我有一个客户和他们的电话号码表。每个客户在三个不同的列（家庭，工作和移动电话）中最多有三个不同的电话号码。手机号码可以用作家庭号码。有些客户与其他客户拥有相同的电话号码，因为他们是夫妻或父母和子女。在这种情况下这是不好的。

因此我需要在移动电话和家庭电话列中搜索dublicates。 （办公电话号码在这里忽略）。

示例表：

ID/Phone home/Phone mobile 
1/12345678/98765432 
2/12345678/22222222 
3/23456789/33333333 
4/33333333/87654321 

在上表中，我需要查询返回所有四个客户。 ID 1和ID 2，因为它们具有相同的家庭电话号码和ID 3和ID 4，因为它们在家庭和移动列中具有相同的号码（33333333）。

我至少需要重复的客户ID，但我希望看到重复的电话号码。

谢谢！

Answer 1

我的做法是将查询规范逼到Id和电话，然后做一个自我联接查找重复：

WITH [CTE] AS (
    SELECT Id, HomePhone as Phone FROM customers 
    UNION 
    SELECT Id, WorkPhone as Phone FROM customers 
    UNION 
    SELECT Id, MobilePhone as Phone FROM customers 
) 
SELECT c1.Id, c2.Id, [c1].[Phone] 
FROM [CTE] AS [c1] INNER JOIN [CTE] AS [c2] 
    ON [c1].[Phone] = [c2].[Phone] AND [c1].[Id] <> [c2].[Id] 

Answer 2

如果您提供输出数据的示例，这将是有用的。 然而，随着EXISTS尝试：https://msdn.microsoft.com/es-es/library/ms188336(v=sql.120).aspx

SELECT * 
FROM customers c 
WHERE EXISTS (SELECT * 
      FROM customers c2 
      WHERE c.Id != c2.Id 
      AND (
        (c.HomePhone = c2.HomePhone || c.HomePhone = c2.WorkPhone || c.HomePhone = c2.MobilePhone) 
        || (c.WorkPhone = c2.HomePhone || c.WorkPhone = c2.WorkPhone || c.WorkPhone = c2.MobilePhone) 
        || (c.MobilePhone= c2.HomePhone || c.MobilePhone= c2.WorkPhone || c.MobilePhone= c2.MobilePhone) 
      ) 

Answer 3

我认为你可以做你想要使用exists什么：

select t.* 
from t 
where exists (select 1 
       from t t2 
       where t2.id <> t.id and 
        (t2.homephone in (t.homephone, mobilephone) or 
        t2.mobilephone in (t.homephone, mobilephone) 
        ) 
      ); 

另一种方法可能有更好的表现：

select tp.* 
from (select t.id, v.phone, 
      count(*) over (partition by id) as cnt 
     from t cross apply 
      (values (t.homephone), (t.mobilephone)) v(phone) 
    ) tp 
where cnt > 1; 

这个版本实际上将返回两列中具有相同电话的行。这很容易解决，如果这可能是一个问题。

Answer 4

你可以UNPIVOT然后将数据做一个自我联接：

CREATE TABLE #phone 
(
    Id INT, 
    HomePhone VARCHAR(20), 
    MobilePhone VARCHAR(20) 
) 

INSERT INTO #phone (Id, HomePhone, MobilePhone) VALUES(1, 12345678, 98765432); 
INSERT INTO #phone (Id, HomePhone, MobilePhone) VALUES(2, 12345678, 22222222); 
INSERT INTO #phone (Id, HomePhone, MobilePhone) VALUES(3, 23456789, 33333333); 
INSERT INTO #phone (Id, HomePhone, MobilePhone) VALUES(4, 33333333, 87654321); 

WITH unpivoted AS 
(
    SELECT Id, Phone, PhoneType 
    FROM #phone 
    UNPIVOT 
    (
     Phone FOR PhoneType IN (HomePhone, MobilePhone) 
    ) AS unpiv 
) 
SELECT a.id, a.Phone, a.PhoneType, b.Id, b.Phone, b.PhoneType 
FROM unpivoted A 
INNER JOIN unpivoted B ON A.Phone = B.Phone AND B.Id > A.Id 

回报

1 12345678 HomePhone 2 12345678 HomePhone 
3 33333333 MobilePhone 4 33333333 HomePhone