在postgresql中搜索跨域重复项

qv7cva1a 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(339)

我有一张联系人表。该表包含一个mobile\u phone列和一个home\u phone列。我想获取所有重复的联系人，其中一个重复是两个联系人共享一个电话号码。如果联系人a的移动电话与联系人b的家庭电话匹配，这也是一个副本。下面是三个应该匹配的联系人的示例。

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

sql postgresql duplicates

来源：https://stackoverflow.com/questions/63275516/search-for-cross-field-duplicates-in-postgresql

2条答案

按热度按时间

hujrc8aj1#

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

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 约束。）

赞(0）回复(0）举报 2021-07-24

pgvzfuti2#

使用窗口功能：

select c.*
from (select c.*, count(*) over (partition by mobile_phone) as cnt
      from contacts c
     ) c
where cnt > 1;

赞(0）回复(0）举报 2021-07-24

我来回答

在postgresql中搜索跨域重复项

2条答案

相关问题

热门标签

最新问答