SQL Server使用Join获取全量数据 实现无消费客户月度销售额显示为0

问题根因 #

你当前的写法存在两个核心问题：

• CROSS JOIN后直接在WHERE子句添加日期匹配条件，相当于把INNER JOIN的逻辑硬写在了WHERE里，所有没有对应消费记录的「客户+月份」组合都会被过滤掉，因为这些组合的fact_table日期字段为NULL，无法满足匹配条件
• GROUP BY里写的是c.intCodEntidade，和SELECT里的c.client_id字段不统一，部分数据库会直接报错，即使运行也会出现逻辑错误

修复方案 #

先构造「全量客户+全量12个月」的笛卡尔积作为主表，再左关联2021年的消费数据，没有匹配到的消费额用COALESCE转成0即可，修正后的SQL如下：

;WITH Months AS
(
    SELECT 1 AS MonthNum
    UNION ALL
    SELECT MonthNum + 1 AS MonthNum FROM Months WHERE MonthNum < 12
),
-- 提取所有需要统计的客户ID，确保无消费的客户也能出现在结果中
AllClients AS
(
    SELECT DISTINCT client_id FROM fact_table
)
SELECT 
    ac.client_id,
    m.MonthNum AS 'Mês2021',
    COALESCE(SUM(f.sales), 0) AS 'v'
FROM AllClients ac
CROSS JOIN Months m
-- 左关联2021年消费数据，关联条件不会过滤主表的全量客户+月份组合
LEFT JOIN fact_table f 
    ON ac.client_id = f.client_id
    AND MONTH(f.date) = m.MonthNum
    AND YEAR(f.date) = 2021
GROUP BY ac.client_id, m.MonthNum
ORDER BY ac.client_id, m.MonthNum

逻辑说明 #

• 新增AllClientsCTE提取全量客户ID，哪怕客户一整年没有任何消费也会被纳入统计范围
• 用LEFT JOIN替代原来的WHERE条件匹配，2021年消费的过滤逻辑写在JOIN关联条件里，不会过滤主表的全量组合
• 用COALESCE把SUM返回的NULL（无消费的情况）转成0，符合报表展示要求

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