SQL查询问题：Campaigns表0注册记录无法显示的解决需求

问题根源 #

你的原查询使用RIGHT JOIN users，只会保留存在用户记录的行。Campaigns表中注册数为0的行没有对应的Users数据，因此无法被包含在结果中。

修改后的完整SQL查询 #

-- 第一部分：处理付费流量（Campaign_ID≠0），保留Campaigns所有记录
SELECT
    c.registration_date AS Registration_Date,
    CASE
        WHEN c.country = 'USA' THEN 'USA'
        ELSE 'Rest of the World'
    END AS Registration_Country,
    'Paid' AS TrafficType,
    c.advertising_channel AS Advertising_Channel,
    c.spend AS Advertising_Spend,
    COUNT(u.registration_id) AS Number_Registrations,
    SUM(IF(u.day7_deposit_amount > 0, 1, 0)) AS Number_Day7_Depositors,
    SUM(u.day7_deposit_amount) AS Accum_Day7_Deposit_Amount,
    SUM(IF(u.day30_deposit_amount > 0, 1, 0)) AS Number_Day30_Depositors,
    SUM(u.day30_deposit_amount) AS Accum_Day30_Deposit_Amount,
    SUM(IF(u.day60_deposit_amount > 0, 1, 0)) AS Number_Day60_Depositors,
    SUM(u.day60_deposit_amount) AS Accum_Day60_Deposit_Amount,
    SUM(IF(u.day90_deposit_amount > 0, 1, 0)) AS Number_Day90_Depositors,
    SUM(u.day90_deposit_amount) AS Accum_Day90_Deposit_Amount,
    SUM(IF(u.day120_deposit_amount > 0, 1, 0)) AS Number_Day120_Depositors,
    SUM(u.day120_deposit_amount) AS Accum_Day120_Deposit_Amount,
    SUM(IF(u.day150_deposit_amount > 0, 1, 0)) AS Number_Day150_Depositors,
    SUM(u.day150_deposit_amount) AS Accum_Day150_Deposit_Amount
FROM campaigns c
LEFT JOIN users u
    ON c.campaign_id = u.campaign_id
    AND c.registration_date = u.registration_date
    AND c.country = u.country
GROUP BY
    c.registration_date,
    c.country,
    c.advertising_channel,
    c.spend

UNION ALL

-- 第二部分：处理自然流量（Campaign_ID=0）
SELECT
    u.registration_date AS Registration_Date,
    CASE
        WHEN u.country = 'USA' THEN 'USA'
        ELSE 'Rest of the World'
    END AS Registration_Country,
    'Organic' AS TrafficType,
    'Organic' AS Advertising_Channel,
    0 AS Advertising_Spend,
    COUNT(u.registration_id) AS Number_Registrations,
    SUM(IF(u.day7_deposit_amount > 0, 1, 0)) AS Number_Day7_Depositors,
    SUM(u.day7_deposit_amount) AS Accum_Day7_Deposit_Amount,
    SUM(IF(u.day30_deposit_amount > 0, 1, 0)) AS Number_Day30_Depositors,
    SUM(u.day30_deposit_amount) AS Accum_Day30_Deposit_Amount,
    SUM(IF(u.day60_deposit_amount > 0, 1, 0)) AS Number_Day60_Depositors,
    SUM(u.day60_deposit_amount) AS Accum_Day60_Deposit_Amount,
    SUM(IF(u.day90_deposit_amount > 0, 1, 0)) AS Number_Day90_Depositors,
    SUM(u.day90_deposit_amount) AS Accum_Day90_Deposit_Amount,
    SUM(IF(u.day120_deposit_amount > 0, 1, 0)) AS Number_Day120_Depositors,
    SUM(u.day120_deposit_amount) AS Accum_Day120_Deposit_Amount,
    SUM(IF(u.day150_deposit_amount > 0, 1, 0)) AS Number_Day150_Depositors,
    SUM(u.day150_deposit_amount) AS Accum_Day150_Deposit_Amount
FROM users u
WHERE u.campaign_id = 0
GROUP BY
    u.registration_date,
    u.country

关键调整说明 #

1. 左连接Campaigns表：使用LEFT JOIN users以Campaigns为主表，确保所有营销活动记录（包括注册数为0的）都被保留。没有对应用户的行，聚合函数会返回0，符合需求。
2. 拆分流量类型：将付费流量和自然流量分开处理，再通过UNION ALL合并结果，保证两类数据都能完整展示。
3. 分组字段优化：付费流量部分使用Campaigns表的字段分组，避免Users表数据为NULL时的分组异常。

内容的提问来源于stack exchange，提问作者m. lekk