如何在SQL中递归计算年度休假结转天数？

这问题确实有点绕，因为每年的可用天数是当年基础额度+上年结转，而结转又依赖于当年的可用天数，属于链式依赖，普通的LAG或者自连接搞不定，得用递归CTE来处理这种逐年递进的计算逻辑。

核心计算逻辑 #

先明确每一步的规则：

• 第一年（该类型最早的年份）：
  • TOTALDAYSALLOWED = NUMDAYSALLOWED（没有上年结转）
  • ROLLOVER = MAX(0, MIN(TOTALDAYSALLOWED - USED, MAXROLLOVER))（结果不能为负，也不能超过最大结转上限）
• 后续年份：
  • TOTALDAYSALLOWED = 当年NUMDAYSALLOWED + 上年ROLLOVER
  • ROLLOVER 按同上公式计算

完整SQL解决方案 #

下面是适配SQL Server/Azure SQL的代码，已经考虑了DAYTYPE的扩展性，支持后续新增类型：

-- 先创建示例表（你提供的代码）
IF OBJECT_ID('tempdb..#COUNTS') IS NOT NULL DROP TABLE #COUNTS
CREATE TABLE #COUNTS (USED INT, DAYTYPE VARCHAR(20), THEYEAR INT)
INSERT INTO #COUNTS (USED, DAYTYPE, THEYEAR)
SELECT 1, 'X', 2019 UNION
SELECT 3, 'X', 2020 UNION
SELECT 0, 'X', 2021

IF OBJECT_ID('tempdb..#ALLOWANCES') IS NOT NULL DROP TABLE #ALLOWANCES
CREATE TABLE #ALLOWANCES (THEYEAR INT, DAYTYPE VARCHAR(20), NUMDAYSALLOWED INT, MAXROLLOVER INT)
INSERT INTO #ALLOWANCES (THEYEAR, DAYTYPE, NUMDAYSALLOWED, MAXROLLOVER)
SELECT 2019, 'X', 3, 3 UNION
SELECT 2020, 'X', 3, 3 UNION
SELECT 2021, 'X', 3, 3

-- 递归CTE计算结转
WITH VacationRollup AS (
    -- 锚点成员：处理每个DAYTYPE的第一年（最小年份）
    SELECT
        c.THEYEAR,
        c.DAYTYPE,
        c.USED,
        a.NUMDAYSALLOWED,
        a.MAXROLLOVER,
        -- 第一年的总可用天数就是基础额度
        CAST(a.NUMDAYSALLOWED AS INT) AS TOTALDAYSALLOWED,
        -- 计算第一年的结转
        CAST(MAX(0, MIN(a.NUMDAYSALLOWED - c.USED, a.MAXROLLOVER)) AS INT) AS ROLLOVER,
        -- HOW列说明
        '第一年，无上年结转' AS HOW
    FROM #COUNTS c
    JOIN #ALLOWANCES a ON c.DAYTYPE = a.DAYTYPE AND c.THEYEAR = a.THEYEAR
    WHERE c.THEYEAR = (SELECT MIN(THEYEAR) FROM #COUNTS WHERE DAYTYPE = c.DAYTYPE)

    UNION ALL

    -- 递归成员：处理后续年份，依赖上一年的结转结果
    SELECT
        curr.THEYEAR,
        curr.DAYTYPE,
        curr.USED,
        curr_a.NUMDAYSALLOWED,
        curr_a.MAXROLLOVER,
        -- 当年总可用 = 当年基础额度 + 上年结转
        CAST(curr_a.NUMDAYSALLOWED + prev.ROLLOVER AS INT) AS TOTALDAYSALLOWED,
        -- 计算当年结转
        CAST(MAX(0, MIN((curr_a.NUMDAYSALLOWED + prev.ROLLOVER) - curr.USED, curr_a.MAXROLLOVER)) AS INT) AS ROLLOVER,
        -- HOW列说明
        '当年可用 = 基础' + CAST(curr_a.NUMDAYSALLOWED AS VARCHAR) + '+上年结转' + CAST(prev.ROLLOVER AS VARCHAR) AS HOW
    FROM #COUNTS curr
    JOIN #ALLOWANCES curr_a ON curr.DAYTYPE = curr_a.DAYTYPE AND curr.THEYEAR = curr_a.THEYEAR
    JOIN VacationRollup prev ON curr.DAYTYPE = prev.DAYTYPE AND curr.THEYEAR = prev.THEYEAR + 1
)

-- 查询最终结果
SELECT * FROM VacationRollup ORDER BY DAYTYPE, THEYEAR

示例结果解释 #

运行上面的代码后，你会得到如下结果：

完全符合规则：

• 2019年：3-1=2，不超过MAXROLLOVER3，结转2
• 2020年：3+2=5，5-3=2，结转2
• 2021年：3+2=5，5-0=5，超过MAXROLLOVER3，所以结转3

扩展性说明 #

这个方案已经支持多DAYTYPE：如果后续新增其他类型（比如Y、Z），只要在#COUNTS和#ALLOWANCES里添加对应数据，递归CTE会自动按DAYTYPE分组计算，互不干扰。

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