SQL Server中用递归CTE实现应收款多日期对批量计算的问询

优先方案：用CROSS APPLY替代递归CTE #

递归CTE更适合处理层级或序列类场景，批量处理多组日期对用**交叉应用（CROSS APPLY）**更高效简洁，无需手动UNION ALL，直接为每组日期生成核销结果。

假设你原有的单组日期核销查询逻辑如下（示例）：

SELECT 
    customer_id,
    SUM(write_off_amount) AS total_write_off
FROM receivables
WHERE transaction_date BETWEEN @start_eomonth AND @end_eomonth
-- 这里保留你原有的所有核销规则（比如匹配应收/实收、过滤条件等）
GROUP BY customer_id

修改为批量处理版本：

SELECT 
    d.start_eomonth_timestamp,
    d.end_eomonth_timestamp,
    r.customer_id,
    r.total_write_off
FROM #runout_dates_tbl d
CROSS APPLY (
    -- 直接复用你的单组核销逻辑，替换变量为日期表的字段
    SELECT 
        customer_id,
        SUM(write_off_amount) AS total_write_off
    FROM receivables
    WHERE transaction_date BETWEEN d.start_eomonth_timestamp AND d.end_eomonth_timestamp
    -- 保留原有的核销计算逻辑（关联匹配、过滤、聚合等）
    GROUP BY customer_id
) r

逻辑说明：

• #runout_dates_tbl中的每一行日期对，都会触发CROSS APPLY内的子查询执行一次
• 子查询直接引用外层日期表的start_eomonth_timestamp和end_eomonth_timestamp作为过滤条件
• 最终结果自动合并所有日期对的核销计算结果，无需额外拼接UNION ALL

特殊场景下的递归CTE方案（不推荐） #

如果因业务限制必须使用递归CTE，可以通过行号遍历日期组实现：

WITH DateCTE AS (
    -- 锚点成员：获取第一组日期并标记行号
    SELECT 
        start_eomonth_timestamp,
        end_eomonth_timestamp,
        ROW_NUMBER() OVER (ORDER BY start_eomonth_timestamp) AS row_num
    FROM #runout_dates_tbl
    UNION ALL
    -- 递归成员：依次取下一组日期
    SELECT 
        d.start_eomonth_timestamp,
        d.end_eomonth_timestamp,
        d.row_num
    FROM (
        SELECT 
            start_eomonth_timestamp,
            end_eomonth_timestamp,
            ROW_NUMBER() OVER (ORDER BY start_eomonth_timestamp) AS row_num
        FROM #runout_dates_tbl
    ) d
    JOIN DateCTE cte ON d.row_num = cte.row_num + 1
)
-- 关联核销查询
SELECT 
    cte.start_eomonth_timestamp,
    cte.end_eomonth_timestamp,
    r.customer_id,
    r.total_write_off
FROM DateCTE cte
CROSS APPLY (
    -- 你的核销逻辑
    SELECT 
        customer_id,
        SUM(write_off_amount) AS total_write_off
    FROM receivables
    WHERE transaction_date BETWEEN cte.start_eomonth_timestamp AND cte.end_eomonth_timestamp
    GROUP BY customer_id
) r
OPTION (MAXRECURSION 0); -- 若日期组数量超过100，需添加此选项解除递归次数限制

注意：

递归CTE的性能远不如CROSS APPLY，仅在特殊场景下使用。

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