MS SQL Server中LEFT JOIN结合GROUP BY实现全量产品月度访问量统计的问题

我来帮你搞定这个统计问题——你的核心痛点是没有先构建所有产品与所有月份的完整组合，直接用日历表左连接统计数据的话，只会保留有匹配记录的行，自然出不来无访问月份的0值行。

原代码的问题分析 #

你的原SQL是用#calendar左连接过滤后的Stats子集，但这个逻辑只能关联到当月有访问数据的产品，没法覆盖到“产品存在但当月无访问”的场景，所以那些需要显示0的行根本没被生成出来。

修正方案：先生成全量组合再左连接 #

正确的思路是：

1. 先提取Stats表中所有唯一的产品；
2. 把这些产品和日历表做交叉连接（CROSS JOIN），得到所有可能的「产品-月份」组合；
3. 再用这个全量组合左连接Stats表，统计每个组合的访问次数，匹配不到的就显示0。

修正后的SQL代码如下：

-- 定义时间范围变量（和你原代码一致）
DECLARE @Start date 
SET @Start = DATEADD(month, DATEDIFF(month, 0, DATEADD(month, -3, GETDATE())), 0) 

-- CTE1：获取所有唯一的产品列表
WITH UniqueProducts AS (
    SELECT DISTINCT ProductID 
    FROM Stats
),
-- CTE2：生成所有产品与所有月份的完整组合
ProductMonthCombos AS (
    SELECT up.ProductID, c.themonth
    FROM UniqueProducts up
    CROSS JOIN #calendar c
    -- 如果需要限制月份范围，在这里加条件，比如：WHERE c.themonth >= @Start
)
-- 左连接统计数据并计算访问量
SELECT 
    pmc.ProductID,
    pmc.themonth,
    ISNULL(COUNT(s.ProductID), 0) AS Total_Views
FROM ProductMonthCombos pmc
LEFT JOIN Stats s 
    ON pmc.ProductID = s.ProductID 
    AND pmc.themonth = s.FirstDayOfMonth
    AND s.FirstDayOfMonth >= @Start -- 时间过滤放在JOIN条件里，避免左连接变内连接
GROUP BY pmc.ProductID, pmc.themonth
ORDER BY pmc.ProductID, pmc.themonth;

关键注意点 #

• 时间过滤条件一定要放在LEFT JOIN的ON子句里，不能放在WHERE里——否则会把左连接中没有匹配到的行过滤掉，等同于内连接，又会丢失需要显示0的行。
• 用DISTINCT提取唯一产品是为了避免重复的产品行，确保每个产品只和每个月份组合一次。

执行后得到的结果 #

运行上述代码后，就能得到你期望的全量统计结果：

Prod1 | 2021-06-01 | 2
Prod1 | 2021-07-01 | 0
Prod1 | 2021-08-01 | 1
Prod2 | 2021-06-01 | 0
Prod2 | 2021-07-01 | 1
Prod2 | 2021-08-01 | 2

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