基于集合的SSMS TSQL债务还款计划生成方案问询

需求说明 #

现有临时表#full，包含ID、StartDate、[Total Debt]、RepaymentRate、[Yr 1 partial Rate]、Amount字段，存储债务总额、年度还款额、还款起始日及首年比例还款额。需生成每个ID的还款计划：

• 首笔还款按[Yr 1 partial Rate]在StartDate支付
• 后续每年按RepaymentRate支付
• 最后一笔支付剩余尾款，直至债务结清

此前采用循环方法未获认可，需基于集合的实现方案（如窗口函数），若不可行可提供替代方案。

测试数据 #

CREATE TABLE #full (
ID NVARCHAR(10),
StartDate DATE,
[Total Debt] INT,
RepaymentRate INT,
[Yr 1 partial Rate] INT,
Amount INT);

INSERT INTO #full
VALUES
(1, '2023-01-01',   100,    24, 2,   0),
(2, '2024-01-01',   100,    11, 5,   0),
(3, '2025-01-01',   100,    32, 7,   0),
(4, '2026-01-01',   100,    9,  2,   0),
(5, '2027-01-01',   100,    5,  24,  0)

集合式实现方案：递归CTE #

递归CTE是SQL中实现这类序列生成的高效集合式方案，无需循环即可生成每笔还款记录：

WITH RepaymentPlans AS (
    -- 初始成员：首笔还款记录
    SELECT 
        ID,
        StartDate AS Start,
        [Total Debt],
        RepaymentRate AS Rate,
        [Yr 1 partial Rate] AS Yr1Rate,
        [Yr 1 partial Rate] AS Amount,
        StartDate AS PayYear,
        -- 计算首笔还款后的剩余债务
        [Total Debt] - [Yr 1 partial Rate] AS RemainingDebt
    FROM #full
    WHERE [Yr 1 partial Rate] > 0
    UNION ALL
    -- 递归成员：后续年度还款记录
    SELECT 
        rp.ID,
        rp.Start,
        rp.[Total Debt],
        rp.Rate,
        rp.Yr1Rate,
        -- 判断当期还款额：剩余债务大于年度还款额则按Rate，否则付尾款
        CASE WHEN rp.RemainingDebt > rp.Rate THEN rp.Rate ELSE rp.RemainingDebt END AS Amount,
        -- 生成下一年还款日期
        DATEADD(YEAR, 1, rp.PayYear) AS PayYear,
        -- 更新剩余债务
        rp.RemainingDebt - CASE WHEN rp.RemainingDebt > rp.Rate THEN rp.Rate ELSE rp.RemainingDebt END AS RemainingDebt
    FROM RepaymentPlans rp
    WHERE rp.RemainingDebt > 0 -- 剩余债务为0时停止递归
)
SELECT 
    ID,
    Start,
    [Total Debt],
    Rate,
    Yr1Rate,
    Amount,
    PayYear
FROM RepaymentPlans
ORDER BY ID, PayYear;

方案说明 #

1. 初始成员：生成首笔还款记录，同步计算首笔还款后的剩余债务
2. 递归成员：基于上一条记录自动生成下一年的还款记录，动态判断当期应还金额（全额年度还款或剩余尾款），直到剩余债务清零
3. 最终结果按ID和还款日期排序，输出完整的还款计划

预期输出示例 #

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