要查找具有（跨列）重复电话号码的所有行，请执行以下操作：
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约束条件下禁止使用的噪音。）
602392714
清零编程群