在PostgreSQL中搜索跨域重复项

admin

在PostgreSQL中搜索跨域重复项

sql

我有一张联系表。该表包含mobile_phone列和home_phone列。我想获取所有重复的联系人，其中重复是两个共享一个电话号码的联系人。如果联系人A的mobile_phone与联系人B的home_phone匹配，则这也是重复项。这是应该匹配的三个联系人的示例。

contact_id|mobile_phone|home_phone|other columns such as email.......|...
-------------------------------------------------------------------------
111       |9748777777  |1112312312|..................................|...
112       |1112312312  |null      |..................................|...
113       |9748777777  |0001112222|..................................|...

这是一个查询，查找重复项，但不跨字段。

select mobile_phone from contacts group by mobile_phone HAVING count(*) > 1 order by mobile_phone

阅读 185

2021-07-01

共1个答案

admin

要查找具有（跨列）重复电话号码的所有行，请执行以下操作：

SELECT *
FROM   contacts c
WHERE  EXISTS (
   SELECT FROM contacts x
   WHERE  x.mobile_phone IN (c.mobile_phone, c.home_phone)
       OR x.home_phone   IN (c.mobile_phone, c.home_phone)
   AND x.contact_id <> c.contact_id  -- except self
   );

要查找两列中所有重复的电话号码，请执行以下操作：

SELECT DISTINCT phone
FROM  (
   SELECT mobile_phone AS phone
   FROM   contacts c
   WHERE  EXISTS (
      SELECT FROM mobile_phone x
      WHERE  c.mobile_phone IN (x.mobile_phone, x.home_phone)
      AND    c.contact_id <> x.contact_id  -- except self
      )
   UNION ALL
   SELECT home_phone
   FROM   contacts c
   WHERE  EXISTS (
      SELECT FROM mobile_phone x
      WHERE  c.home_phone = x.home_phone   -- cross-over covered by 1s SELECT
      AND    c.contact_id <> x.contact_id  -- except self
      )
   ) sub;

在 同一行的 两列中重复相同的数字不符合条件。我认为您不希望包含这些内容。（仍然会是一些值得在CHECK约束条件下禁止使用的噪音。）

2021-07-01