在PostgreSQL中搜索跨域重复项

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

SELECt *FROM   contacts cWHERe  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 phoneFROM  (   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