T-SQL递归CTE按月计算文档库存问题求解

问题背景 #

需要基于T-SQL计算2019年至今每个月的文档库存，要求将查询中SELECT列和WHERE子句内所有EOMONTH(@VarDate, 0)按月份迭代为EOMONTH(@VarDate, n+1)，最终输出每个月末对应的在库Expediente（档案编号）列表，预期输出结构如下：

已尝试方案的错误原因 #

第一版递归CTE #

第一版代码仅迭代了VarDate日期值，WHERE子句的筛选逻辑没有随迭代日期更新，导致所有月份返回的档案集合和首月完全一致，错误代码如下：

DECLARE @VarDate date
SET @VarDate = '2019-01-01';

DECLARE @EndDate date
SET @EndDate = EOMONTH(@VarDate,3);

WITH cte AS
(
    SELECT
        CAST(numexp AS nvarchar) + '/' + CAST(añoexp AS nvarchar) Expediente, 
        EOMONTH(@VarDate, 0) VarDate
    FROM 
        syn_TControl_Movimientos_expediente m
    WHERE
        m.fase = 1 
        AND (m.Fecha <= EOMONTH(@VarDate, 0) AND (m.CveMov IN (1,3))
        AND (m.ConsMov = (SELECT MAX(ConsMov) AS Expr1 
                          FROM syn_TControl_Movimientos_Expediente AS m2
                          WHERE (NumExp = m.NumExp) 
                            AND (AñoExp = m.AñoExp) 
                            AND (Fecha <= EOMONTH(@VarDate, 0))))) 

    UNION ALL

    SELECT
        c.Expediente, EOMONTH(c.VarDate, 1) 
    FROM
        cte AS c
    WHERE 
        EOMONTH(c.VarDate, 1) <= @EndDate
)
SELECT * FROM cte

错误表现：所有月份的档案列表和2019年1月完全相同，仅日期字段按月递增。

第二版递归CTE #

第二版尝试在递归部分关联源表做聚合查询，触发SQL Error [467] [S0001]报错：

GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression 'cte'

错误代码如下：

DECLARE @VarDate date
SET @VarDate = '2019-01-01';

DECLARE @EndDate date
SET @EndDate = EOMONTH(@VarDate,3);

WITH cte AS
(
    SELECT
        CAST(numexp AS nvarchar) + '/' + CAST(añoexp AS nvarchar) Expediente, 
        EOMONTH(@VarDate, 0) VarDate
    FROM 
        syn_TControl_Movimientos_expediente m
    WHERE 
        m.fase = 1 
        AND (m.Fecha <= EOMONTH(@VarDate, 0) AND (m.CveMov IN (1,3))
        AND (m.ConsMov = (SELECT MAX(ConsMov) AS Expr1 
                          FROM syn_TControl_Movimientos_Expediente AS m2
                          WHERE (NumExp = m.NumExp) 
                            AND (AñoExp = m.AñoExp) 
                            AND (Fecha <= EOMONTH(@VarDate, 0))))) 

    UNION ALL

    SELECT
        CAST(numexp AS nvarchar) + '/' + CAST(añoexp AS nvarchar) Expediente, 
        EOMONTH(c.VarDate, 1) VarDate
    FROM 
        syn_TControl_Movimientos_expediente m, cte c
    WHERE
        m.fase = 1 
        AND (m.Fecha <= EOMONTH(c.VarDate, 1) AND (m.CveMov IN (1,3))
        AND (m.ConsMov = (SELECT MAX(ConsMov) AS Expr1 
                          FROM syn_TControl_Movimientos_Expediente AS m2
                          WHERE (NumExp = m.NumExp) 
                            AND (AñoExp = m.AñoExp) 
                            AND (Fecha <= EOMONTH(c.VarDate, 1)))))
        AND EOMONTH(c.VarDate, 1) <= @EndDate
)
SELECT * FROM cte

报错根因：T-SQL递归CTE的递归成员部分明确禁止使用聚合函数、GROUP BY等运算。

可行实现方案 #

递归CTE不适合承载逐日期的聚合筛选逻辑，正确思路是拆分逻辑：单独用递归CTE生成所有待统计的月末日期序列（这部分无聚合无复杂关联，完全符合递归语法规则），再通过集合运算批量计算每个月末的在库档案，避开递归部分的语法限制。

实现代码 #

-- 配置统计周期
DECLARE @StartDate DATE = '2019-01-01';
DECLARE @EndDate DATE = GETDATE(); -- 默认统计到当前月月末，可改为固定日期例如'2024-12-31'

-- 1. 递归生成所有待统计的月末日期
WITH DateList AS (
    SELECT EOMONTH(@StartDate, 0) AS StatDate
    UNION ALL
    SELECT EOMONTH(StatDate, 1)
    FROM DateList
    WHERE EOMONTH(StatDate, 1) <= EOMONTH(@EndDate, 0)
),
-- 2. 计算每个档案在每个统计月末的最后一条流水记录编号
ArchiveLastLog AS (
    SELECT
        CAST(m.NumExp AS NVARCHAR) + '/' + CAST(m.AñoExp AS NVARCHAR) AS Expediente,
        dl.StatDate AS VarDate,
        MAX(m.ConsMov) AS LastConsMov
    FROM DateList dl
    JOIN syn_TControl_Movimientos_expediente m
        ON m.Fecha <= dl.StatDate
    WHERE m.fase = 1
    GROUP BY m.NumExp, m.AñoExp, dl.StatDate
)
-- 3. 关联流水表筛选在库状态的档案
SELECT
    all_log.Expediente,
    all_log.VarDate
FROM ArchiveLastLog all_log
JOIN syn_TControl_Movimientos_expediente m
    ON all_log.LastConsMov = m.ConsMov
    AND CAST(m.NumExp AS NVARCHAR) + '/' + CAST(m.AñoExp AS NVARCHAR) = all_log.Expediente
WHERE m.CveMov IN (1,3) -- 对应入库/在库的移动类型编码
ORDER BY all_log.VarDate, all_log.Expediente
OPTION (MAXRECURSION 1000); -- 最多支持1000个月（超80年）的统计范围，完全覆盖需求

优化说明 #

• 该写法为集合化批量计算，性能远高于逐行递归的写法，数据量大时可以给syn_TControl_Movimientos_expediente表添加(NumExp, AñoExp, Fecha, ConsMov, CveMov)的联合索引，查询速度会有明显提升
• 调整统计范围时，仅需修改@StartDate和@EndDate的赋值即可，不需要改动其他核心逻辑

内容的提问来源于stack exchange，提问作者Richard Steefanhöffen