在多个电子邮件字段中查找重复项

Question

我有一个奇怪的要求。我必须在我的数据库中找到重复的联系人记录（应该很简单），我的问题是我必须按名字、姓氏和任何相互匹配的电子邮件字段进行匹配。

Exemple:
FirstName    | LastName    | Email           | WorkEmail            | AnotherEmail
John           Smith          [e-mail]         [e-mail]          yougettheIdea.com
John           Smith          [e-mail]                              
John           Smith          imAdifferent.jh.com

在本例中，我需要确定第1行和第2行中的John Smith是重复记录，但第3行中不是。基本上，我需要查询与FirstName匹配的FirstName、与LastName匹配的LastName以及与任何字段匹配的任何电子邮件字段。。。这可能吗？

我得到这个是为了匹配名字和姓氏，但电子邮件对我来说太多了：

SELECT * FROM
      (SELECT "FirstName","LastName","Email",","WorkEmail","AnotherEmail", count(*)
      OVER
        (PARTITION BY
          "FirstName",
          "LastName"
        ) AS count
      FROM Contact) tableWithCount
      WHERE tableWithCount.count > 1 ORDER BY count DESC;

Answer 1

我会在这里使用exists逻辑，将每个电子邮件列与每个其他列进行检查。以下查询标识所有没有重复记录的记录。

SELECT *
FROM Contact c1
WHERE NOT EXISTS (SELECT 1 FROM Contact c2
                  WHERE c2.LastName = c1.LastName AND c2.FirstName = c1.FirstName AND
                        c2.id <> c1.id AND  -- assuming there is a PK column id
                        (c2.Email = c1.Email OR c2.WorkEmail = c1.Email OR
                         c2.AnotherEmail = c1.Email
                         OR
                         c2.Email = c1.WorkEmail OR c2.WorkEmail = c1.WorkEmail OR
                         c2.AnotherEmail = c1.WorkEmail
                         OR
                         c2.Email = c1.AnotherEmail OR c2.WorkEmail = c1.AnotherEmail OR
                         c2.AnotherEmail = c1.AnotherEmail));

如果您想查找所有重复记录，则将“不存在”更改为“存在”。

Answer 2

我将构建一个电子邮件列数组，然后使用contains操作符检查重复项@

select *
from contact c1
where exists (select * 
              from contact c2
              where (c1.first_name, c1.last_name) = (c2.first_name, c2.last_name)
                and (    
                    array_remove(array[c1.email, c1.work_email, c1.another_email],null) @> array_remove(array[c2.email, c2.work_email, c2.another_email], null)
                 or array_remove(array[c1.email, c1.work_email, c1.another_email],null) <@ array_remove(array[c2.email, c2.work_email, c2.another_email], null) 
                )
                and c1.ctid <> c2.ctid
              );

表达式为c1。ctid

在线示例