递归CTE计算PersonsAlive遇最大递归耗尽问题求助

问题背景 #

需求是基于上一行的PersonsAlive与当前行的SurvivalProbablity，计算各年龄对应的存活人数：Age=0时初始值为100000，年龄最大到85。最初编写的递归CTE出现「Max Recursion Exhausted before Statement Completion」错误，调整后虽能运行，但结果不符合预期。

原查询错误分析 #

1. 原始查询递归无限循环：JOIN条件写的是p.Age = c.Age，导致每次递归都关联同年龄的CTE行，永远满足递归条件，直到达到系统递归次数上限报错。
2. 调整后查询逻辑错误：改用p.Age = c.Age +1后，错误使用了LAG(c.PersonsAlive,1)窗口函数。递归CTE的每次迭代仅处理上一层的结果，窗口函数无法正确获取递归过程中生成的上一行存活人数，最终结果不符合预期。

正确实现方案 #

递归CTE的核心是用前一轮递归得到的存活人数，直接乘以当前行的生存概率，无需窗口函数，通过递归逐步推进年龄即可。

正确代码 #

WITH CTE AS (
    -- 锚点成员：初始化Age=0的存活人数为100000
    SELECT 
        ZipCode,
        Age,
        [Population],
        Deaths,
        DeathRate,
        Death_Proportion,
        DeathProbablity,
        SurvivalProbablity,
        CAST(100000 AS DECIMAL(18,4)) AS PersonsAlive  -- 用高精度类型避免精度丢失
    FROM ProbabilityTable
    WHERE Age = 0

    UNION ALL

    -- 递归成员：关联上一年龄的CTE结果，计算当前年龄存活人数
    SELECT 
        p.ZipCode,
        p.Age,
        p.[Population],
        p.Deaths,
        p.DeathRate,
        p.Death_Proportion,
        p.DeathProbablity,
        p.SurvivalProbablity,
        CAST(c.PersonsAlive * p.SurvivalProbablity AS DECIMAL(18,4)) AS PersonsAlive
    FROM ProbabilityTable p
    INNER JOIN CTE c
        ON p.ZipCode = c.ZipCode
        AND p.Age = c.Age + 1  -- 每次递归推进一个年龄
    WHERE p.Age <= 85  -- 覆盖到最大年龄85
)
SELECT * FROM CTE ORDER BY ZipCode, Age;

关键改进说明 #

• 锚点成员显式设置初始存活人数为100000，确保初始值正确。
• 递归成员直接使用上一轮CTE的PersonsAlive计算当前值，逻辑清晰且符合需求。
• JOIN条件p.Age = c.Age +1保证递归按年龄递增推进，不会出现无限循环。
• 转换为DECIMAL(18,4)类型，避免整数乘法导致的精度丢失或截断问题。

递归次数限制处理（可选） #

SQL Server默认递归次数上限为100，计算到Age=85仅需85次递归，完全满足。若后续需求扩展到更大年龄，可在查询末尾添加参数取消限制：

SELECT * FROM CTE ORDER BY ZipCode, Age OPTION (MAXRECURSION 0);

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