SQL Server 2008 R2月度销售同比及补全缺失月份需求

嘿，我完全懂你现在的困境——既要补全所有邮编/门店组合的月度空记录（无数据填0），又要和上年同月做指标对比，还得适配SQL Server 2008 R2的特性。咱们一步步拆解来实现：

核心思路 #

要达成需求，关键是先构建全量的维度组合（所有邮编/门店/年月的笛卡尔积），再通过左连接补全销售数据的空值，最后用自连接获取上年同月的对比数据。你之前用日历表出问题，大概率是把筛选条件放在了WHERE子句里，导致左连接被“降级”成内连接，直接丢失了无数据的月份记录。

分步实现代码 #

1. 生成时间范围内的所有月份（替代日历表） #

SQL Server 2008 R2没有DATEFROMPARTS，我们用递归CTE生成2016年1月至今（或你指定的结束日期）的所有月份：

WITH MonthList AS (
    SELECT 
        CAST('2016-01-01' AS DATE) AS MonthStart,
        DATEPART(YEAR, '2016-01-01') AS TheYear,
        DATEPART(MM, '2016-01-01') AS MonthNum,
        DATENAME(MONTH, '2016-01-01') AS TheMonth
    UNION ALL
    SELECT 
        DATEADD(MONTH, 1, ml.MonthStart),
        DATEPART(YEAR, DATEADD(MONTH, 1, ml.MonthStart)),
        DATEPART(MM, DATEADD(MONTH, 1, ml.MonthStart)),
        DATENAME(MONTH, DATEADD(MONTH, 1, ml.MonthStart))
    FROM MonthList ml
    WHERE DATEADD(MONTH, 1, ml.MonthStart) <= GETDATE() -- 可替换为你需要的结束日期
),

2. 获取所有唯一的邮编/门店组合 #

从现有业务数据中提取不重复的邮编（取前5位）和门店组合：

ZipStoreCombos AS (
    SELECT DISTINCT
        LEFT(a.Zip, 5) AS ZipCode,
        s.Store
    FROM dbo.DailySales s
    INNER JOIN dbo.Accounts a ON a.AccountNumber = s.AccountNumber
    WHERE s.SaleType = 3 
      AND s.MovementDate > '2016-01-01' 
      AND ISNULL(a.Zip, '') <> ''
    -- 提示：如果系统有单独的Stores/ZipCodes表，建议直接从这些表取全量组合，确保无销售记录的门店/邮编也能生成月度数据
),

3. 构建全量维度交叉表 #

把月份列表和邮编/门店组合交叉连接，得到所有需要的记录框架：

FullDimension AS (
    SELECT
        zsc.ZipCode,
        zsc.Store,
        ml.TheYear,
        ml.MonthNum,
        ml.TheMonth
    FROM ZipStoreCombos zsc
    CROSS JOIN MonthList ml
),

4. 计算原始销售统计数据 #

先按邮编/门店/年月聚合销售核心指标，这里用内连接筛选有效业务数据：

SalesStats AS (
    SELECT 
        LEFT(a.Zip, 5) AS ZipCode,
        s.Store,
        DATEPART(YEAR, s.MovementDate) AS TheYear,
        DATEPART(MM, s.MovementDate) AS MonthNum,
        SUM(s.Dollars) AS Sales,
        COUNT(*) AS TxnCount,
        COUNT(DISTINCT s.AccountNumber) AS NumOfAccounts
    FROM dbo.DailySales s
    INNER JOIN dbo.Accounts a ON a.AccountNumber = s.AccountNumber
    WHERE s.SaleType = 3 
      AND s.MovementDate > '2016-01-01' 
      AND ISNULL(a.Zip, '') <> ''
    GROUP BY LEFT(a.Zip, 5), s.Store, DATEPART(YEAR, s.MovementDate), DATEPART(MM, s.MovementDate)
)

5. 关联数据并添加上年同月对比 #

用左连接把全量维度和销售统计关联，同时自连接销售统计表匹配上年同月的数据：

SELECT
    fd.ZipCode,
    fd.Store,
    fd.TheMonth,
    fd.TheYear,
    fd.MonthNum,
    ISNULL(ss.Sales, 0) AS Sales,
    ISNULL(ss.TxnCount, 0) AS TxnCount,
    ISNULL(ss.NumOfAccounts, 0) AS NumOfAccounts,
    -- 匹配上年同月的指标
    ISNULL(ss_prev.Sales, 0) AS Sales_LY,
    ISNULL(ss_prev.TxnCount, 0) AS TxnCount_LY,
    ISNULL(ss_prev.NumOfAccounts, 0) AS NumOfAccounts_LY
FROM FullDimension fd
LEFT JOIN SalesStats ss 
    ON fd.ZipCode = ss.ZipCode
    AND fd.Store = ss.Store
    AND fd.TheYear = ss.TheYear
    AND fd.MonthNum = ss.MonthNum
LEFT JOIN SalesStats ss_prev
    ON fd.ZipCode = ss_prev.ZipCode
    AND fd.Store = ss_prev.Store
    AND fd.TheYear - 1 = ss_prev.TheYear
    AND fd.MonthNum = ss_prev.MonthNum
ORDER BY fd.ZipCode, fd.Store, fd.TheYear, fd.MonthNum;

关键注意事项 #

• 不要破坏左连接逻辑：如果把s.SaleType = 3这类筛选条件放在主查询的WHERE里，会直接过滤掉左连接后无数据的记录，所以必须把筛选条件放在聚合统计的SalesStats子查询中。
• 适配SQL Server 2008 R2：因为2008 R2不支持LAG()这类窗口函数，所以用自连接的方式获取上年同月数据是最兼容的写法。
• 全量组合的准确性：如果系统有单独的基础表（比如Stores、ZipCodes），优先从这些表取全量组合，这样即使某个邮编/门店从未产生过销售，也会生成月度空记录并填充0。

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