如何用SQL查询成员参与多Campaign的重叠情况（无需大量交叉连接）

不用手动写大量交叉连接的核心思路是先将宽表转为长表，再通过分组或自连接生成活动组合，以下是具体实现：

1. 把宽表转成活动-成员的长表 #

用UNION ALL把每个活动列拆成单独的行，让每个成员参与的每个活动对应一条记录，适配各类SQL数据库：

WITH campaign_members AS (
    SELECT Patient_AGN, 'MCP' AS campaign_name
    FROM your_table
    WHERE MCP = 1
    UNION ALL
    SELECT Patient_AGN, 'P5E' AS campaign_name
    FROM your_table
    WHERE P5E = 1
    UNION ALL
    SELECT Patient_AGN, 'CMD' AS campaign_name
    FROM your_table
    WHERE CMD = 1
    UNION ALL
    SELECT Patient_AGN, 'KRG' AS campaign_name
    FROM your_table
    WHERE KRG = 1
    UNION ALL
    SELECT Patient_AGN, 'EMP' AS campaign_name
    FROM your_table
    WHERE EMP = 1
    UNION ALL
    SELECT Patient_AGN, 'CAR' AS campaign_name
    FROM your_table
    WHERE CAR = 1
    UNION ALL
    SELECT Patient_AGN, 'SEG' AS campaign_name
    FROM your_table
    WHERE SEG = 1
    UNION ALL
    SELECT Patient_AGN, 'CON' AS campaign_name
    FROM your_table
    WHERE CON = 1
    UNION ALL
    SELECT Patient_AGN, 'EMB' AS campaign_name
    FROM your_table
    WHERE EMB = 1
    UNION ALL
    SELECT Patient_AGN, 'HP' AS campaign_name
    FROM your_table
    WHERE HP = 1
)

2. 生成重叠活动组合 #

情况1：两两活动的重叠统计 #

如果只需要统计任意两个活动的共同参与人数，用自连接+分组即可，通过c1.campaign_name < c2.campaign_name避免重复组合（比如MCP&P5E和P5E&MCP只算一次）：

SELECT 
    c1.campaign_name AS campaign1,
    c2.campaign_name AS campaign2,
    COUNT(DISTINCT c1.Patient_AGN) AS overlapping_members_count
FROM campaign_members c1
JOIN campaign_members c2 
    ON c1.Patient_AGN = c2.Patient_AGN 
    AND c1.campaign_name < c2.campaign_name
GROUP BY c1.campaign_name, c2.campaign_name
ORDER BY overlapping_members_count DESC;

针对你提供的示例数据，这个查询会返回：

情况2：任意数量活动的重叠统计 #

如果需要统计所有2个及以上活动的重叠组合（比如同时参与MCP、CON、SEG的成员），可以先拼接每个成员的活动列表，再分组统计：

以MySQL为例：

WITH member_campaigns AS (
    SELECT 
        Patient_AGN,
        GROUP_CONCAT(campaign_name ORDER BY campaign_name SEPARATOR ', ') AS campaign_combination
    FROM campaign_members
    GROUP BY Patient_AGN
    HAVING COUNT(campaign_name) >= 2
)
SELECT 
    campaign_combination,
    COUNT(Patient_AGN) AS overlapping_members_count
FROM member_campaigns
GROUP BY campaign_combination
ORDER BY overlapping_members_count DESC;

以SQL Server/PostgreSQL为例：

把GROUP_CONCAT替换为STRING_AGG(campaign_name, ', ') WITHIN GROUP (ORDER BY campaign_name)即可。

方案优势 #

只需要在第一步的UNION ALL中添加对应活动列的查询，后续逻辑无需修改，不管活动数量是10个还是更多，都不用手动编写大量交叉连接语句。

内容的提问来源于stack exchange，提问作者PMiaz