SQL Server如何查询三个不同表中重复出现的Phone值及对应Place

现有测试表及数据 #

三张SQL Server数据表的测试插入语句如下：

INSERT INTO SOC(Phone,Network,Place)
VALUES(8934,'Landline','Miyapur'), (8962,'Jio','Miyapur'), (9033,'voda','Miyapur'), (9032,'Bsnl','Miyapur');

Insert INTO SOC_1(Phone,Network,Place)
VALUES(8934,'Airtel','SRNagar'), (9052,'Jio','SRNagar'), (1000,'voda','SRNagar'), (2000,'Bsnl','SRNagar');

insert INTO SOC_2(Phone,Network,Place)
VALUES(9052,'jio','kutply'), (9033,'voda','kutply'), (8934,'Airtel','kutply'), (3000,'Bsnl','kutply');

需求 #

查询出在至少两张表中出现过的Phone值，以及其对应的Place字段，期望输出如下：

原语句错误原因 #

你原使用的三表INNER JOIN语句仅会匹配同时存在于三张表的Phone，只有8934符合该条件，因此仅返回1条结果，不符合需求。

正确查询语句 #

WITH QualifiedPhones AS (
    -- 筛选出至少在2张表中出现过的Phone
    SELECT Phone
    FROM (
        SELECT DISTINCT Phone FROM SOC
        UNION ALL
        SELECT DISTINCT Phone FROM SOC_1
        UNION ALL
        SELECT DISTINCT Phone FROM SOC_2
    ) t
    GROUP BY Phone
    HAVING COUNT(*) >= 2
),
AllPhonePlaces AS (
    -- 汇总所有表的Phone和Place对应关系
    SELECT Phone, Place FROM SOC
    UNION ALL
    SELECT Phone, Place FROM SOC_1
    UNION ALL
    SELECT Phone, Place FROM SOC_2
)
SELECT app.Phone, app.Place
FROM AllPhonePlaces app
INNER JOIN QualifiedPhones qp ON app.Phone = qp.Phone
ORDER BY app.Phone DESC, app.Place;

内容的提问来源于stack exchange，提问作者CDR Cell Cyberabad