SQL Server 2017获取各月末会员数并补全空白月份数据实现问题

你的原有查询只能返回存在变动记录的月份数据，核心缺失的逻辑是生成每个中心需要覆盖的所有连续月份行，再对空白行的会员数做历史值填充。完整实现逻辑如下：

实现思路 #

1. 先计算每个中心的日期范围：取每个中心首次变动记录的月末到末次变动记录的月末，作为该中心需要展示的月份区间
2. 生成每个中心对应区间内的所有连续月末日期，得到完整的待填充行
3. 关联原表中每个月最后一条的会员数记录，没有变动的月份对应会员数为NULL
4. 用窗口函数填充NULL值，取每个中心当前日期之前最新的非空会员数

完整SQL代码 #

WITH GetCenterRange AS (
    -- 计算每个中心需要覆盖的月份起止范围
    SELECT 
        CenterId,
        MIN(EOMONTH(Change_date)) AS MinEOM,
        MAX(EOMONTH(Change_date)) AS MaxEOM
    FROM #tempCenters
    GROUP BY CenterId
),
AllMonthEOM AS (
    -- 递归生成每个中心所有连续月份的月末日期
    SELECT CenterId, MinEOM AS EOM_Date, MaxEOM
    FROM GetCenterRange
    UNION ALL
    SELECT CenterId, EOMONTH(DATEADD(MONTH, 1, EOM_Date)), MaxEOM
    FROM AllMonthEOM
    WHERE EOM_Date < MaxEOM
),
MonthlyLastMember AS (
    -- 提取原表中每个月最后一条的会员数记录，和原有逻辑一致
    SELECT 
        t.CenterId,
        EOMONTH(t.Change_date) AS EOM_Date,
        t.members
    FROM #tempCenters t
    INNER JOIN (
        SELECT 
            CenterId,
            MAX(OperationId) AS LastOpId
        FROM #tempCenters
        GROUP BY CenterId, YEAR(Change_date), MONTH(Change_date)
    ) s ON t.OperationId = s.LastOpId
)
-- 最终查询，填充空白月份的会员数
SELECT 
    a.CenterId,
    LAST_VALUE(m.members) IGNORE NULLS OVER (
        PARTITION BY a.CenterId 
        ORDER BY a.EOM_Date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Members,
    a.EOM_Date
FROM AllMonthEOM a
LEFT JOIN MonthlyLastMember m 
    ON a.CenterId = m.CenterId AND a.EOM_Date = m.EOM_Date
ORDER BY a.CenterId, a.EOM_Date
-- 如果月份跨度超过100个月，加上下面的配置取消递归层数限制
-- OPTION (MAXRECURSION 0)

结果说明 #

上述查询返回的结果和你期望的逻辑完全一致，示例中写的2020-4-31这类日期属于笔误，SQL Server内置的EOMONTH函数会自动返回对应月份正确的月末日期（比如4月返回30日，2月返回28/29日）。

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