如何将多张表关联至中心表并按列展示唯一结果[SQL]

你的需求完全可行，核心问题在于多表内联（JOIN）会过滤掉不满足所有关联条件的数据，而单表关联时仅需满足单个匹配条件，因此能正常返回结果。以下是两种高效的解决方式：

方案一：条件聚合+左连接（简洁高效） #

通过合并外围表查询，结合CASE表达式在同一聚合逻辑中完成多维度统计，避免重复关联操作：

WITH active_campaign AS (
    SELECT 
        fr.campaign_id, 
        DATE_TRUNC(fr.day, MONTH) AS month, 
        SUM(fr.imps_count) AS sum_of_impressions, 
        advertiser_id 
    FROM `some_table` AS fr
    WHERE day BETWEEN (CURRENT_DATE() - 365) AND (CURRENT_DATE() - EXTRACT(DAY FROM CURRENT_DATE()) + 1)
    GROUP BY fr.campaign_id, month, advertiser_id 
    HAVING SUM(fr.imps_count) > 100
),
profit_centers AS (
    -- 合并所有外围表的筛选逻辑，减少重复代码
    SELECT id, profitCenter
    FROM some_table2 AS camps
    WHERE camps.profitCenter IN ('JPN', 'A', 'B', 'C')
)

SELECT 
    ac.month,
    -- 按利润中心分组统计唯一ID数量，无匹配时返回0
    COUNT(DISTINCT CASE WHEN pc.profitCenter = 'JPN' THEN pc.id END) AS JPN,
    COUNT(DISTINCT CASE WHEN pc.profitCenter = 'A' THEN pc.id END) AS A,
    COUNT(DISTINCT CASE WHEN pc.profitCenter = 'B' THEN pc.id END) AS B,
    COUNT(DISTINCT CASE WHEN pc.profitCenter = 'C' THEN pc.id END) AS C
FROM active_campaign ac
-- 左连接保留中心表所有月份数据
LEFT JOIN profit_centers pc 
    ON ac.advertiser_id = pc.id
GROUP BY ac.month
ORDER BY ac.month DESC;

方案二：预统计维度关联（逻辑清晰） #

先分别统计每个外围表的月度数据，再基于中心表的月份维度进行关联，确保所有月份都被保留：

WITH active_campaign AS (
    SELECT 
        fr.campaign_id, 
        DATE_TRUNC(fr.day, MONTH) AS month, 
        SUM(fr.imps_count) AS sum_of_impressions, 
        advertiser_id 
    FROM `some_table` AS fr
    WHERE day BETWEEN (CURRENT_DATE() - 365) AND (CURRENT_DATE() - EXTRACT(DAY FROM CURRENT_DATE()) + 1)
    GROUP BY fr.campaign_id, month, advertiser_id 
    HAVING SUM(fr.imps_count) > 100
),
-- 提取中心表所有月份作为基础维度
all_months AS (
    SELECT DISTINCT month FROM active_campaign
),
-- 单独统计每个利润中心的月度唯一ID数
pc_jpn_stats AS (
    SELECT 
        ac.month,
        COUNT(DISTINCT pc_jpn.id) AS JPN
    FROM active_campaign ac
    LEFT JOIN (SELECT id FROM some_table2 WHERE profitCenter = 'JPN') pc_jpn
        ON ac.advertiser_id = pc_jpn.id
    GROUP BY ac.month
),
pc_a_stats AS (
    SELECT 
        ac.month,
        COUNT(DISTINCT pc_a.id) AS A
    FROM active_campaign ac
    LEFT JOIN (SELECT id FROM some_table2 WHERE profitCenter = 'A') pc_a
        ON ac.advertiser_id = pc_a.id
    GROUP BY ac.month
),
pc_b_stats AS (
    SELECT 
        ac.month,
        COUNT(DISTINCT pc_b.id) AS B
    FROM active_campaign ac
    LEFT JOIN (SELECT id FROM some_table2 WHERE profitCenter = 'B') pc_b
        ON ac.advertiser_id = pc_b.id
    GROUP BY ac.month
),
pc_c_stats AS (
    SELECT 
        ac.month,
        COUNT(DISTINCT pc_c.id) AS C
    FROM active_campaign ac
    LEFT JOIN (SELECT id FROM some_table2 WHERE profitCenter = 'C') pc_c
        ON ac.advertiser_id = pc_c.id
    GROUP BY ac.month
)

SELECT 
    am.month,
    -- 用COALESCE将NULL转换为0，保证数据可读性
    COALESCE(jpn.JPN, 0) AS JPN,
    COALESCE(a.A, 0) AS A,
    COALESCE(b.B, 0) AS B,
    COALESCE(c.C, 0) AS C
FROM all_months am
LEFT JOIN pc_jpn_stats jpn ON am.month = jpn.month
LEFT JOIN pc_a_stats a ON am.month = a.month
LEFT JOIN pc_b_stats b ON am.month = b.month
LEFT JOIN pc_c_stats c ON am.month = c.month
ORDER BY am.month DESC;

关键说明 #

• 替换原有的内联（JOIN）为左连接（LEFT JOIN），可保留中心表的所有月份数据，避免因无全表共同匹配数据而返回空值。
• 两种方案均通过COUNT(DISTINCT ...)确保统计的是唯一ID，避免重复计数；方案二额外用COALESCE将空值转换为0，提升结果可读性。

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