SQL多笔贷款摊销计划表生成问题（无函数创建权限）

用递归CTE（Common Table Expression）可以批量生成多笔贷款的摊销计划，完全不需要创建函数，仅靠查询权限就能实现。以下是适配通用样本贷款表的完整方案：

假设样本贷款表结构 #

如果你的实际表字段不同，替换对应名称即可：

-- 示例贷款表（仅用于说明，你无需创建，直接用现有表）
CREATE TABLE Loans (
    LoanID INT PRIMARY KEY,
    Principal DECIMAL(18,2), -- 贷款本金
    AnnualInterestRate DECIMAL(5,2), -- 年利率（如4.5代表4.5%）
    LoanMonths INT, -- 总贷款期限（月数）
    StartDate DATE -- 贷款起始日期
);

批量摊销计划生成SQL #

WITH AmortizationCTE AS (
    -- 锚点成员：生成每笔贷款的第一期数据
    SELECT
        LoanID,
        1 AS InstallmentNumber,
        StartDate AS InstallmentDate,
        Principal AS InitialPrincipal,
        Principal AS RemainingPrincipal,
        (AnnualInterestRate / 12) / 100 AS MonthlyRate,
        -- 等额本息月供计算（如需等额本金，替换此逻辑）
        ROUND(
            Principal * ((AnnualInterestRate/12/100) * POWER(1 + (AnnualInterestRate/12/100), LoanMonths)) 
            / (POWER(1 + (AnnualInterestRate/12/100), LoanMonths) - 1),
            2
        ) AS MonthlyPayment,
        ROUND(Principal * (AnnualInterestRate/12/100), 2) AS InterestPayment,
        ROUND(
            (Principal * ((AnnualInterestRate/12/100) * POWER(1 + (AnnualInterestRate/12/100), LoanMonths)) 
            / (POWER(1 + (AnnualInterestRate/12/100), LoanMonths) - 1)) 
            - (Principal * (AnnualInterestRate/12/100)),
            2
        ) AS PrincipalPayment
    FROM Loans
    WHERE LoanMonths > 0

    UNION ALL

    -- 递归成员：生成后续每一期数据
    SELECT
        ac.LoanID,
        ac.InstallmentNumber + 1 AS InstallmentNumber,
        DATEADD(MONTH, 1, ac.InstallmentDate) AS InstallmentDate,
        ac.InitialPrincipal,
        ROUND(ac.RemainingPrincipal - ac.PrincipalPayment, 2) AS RemainingPrincipal,
        ac.MonthlyRate,
        ac.MonthlyPayment,
        ROUND((ac.RemainingPrincipal - ac.PrincipalPayment) * ac.MonthlyRate, 2) AS InterestPayment,
        -- 最后一期修正本金，避免四舍五入误差
        CASE
            WHEN ac.InstallmentNumber + 1 = l.LoanMonths THEN ROUND(ac.RemainingPrincipal - ac.PrincipalPayment, 2)
            ELSE ROUND(ac.MonthlyPayment - ROUND((ac.RemainingPrincipal - ac.PrincipalPayment) * ac.MonthlyRate, 2), 2)
        END AS PrincipalPayment
    FROM AmortizationCTE ac
    JOIN Loans l ON ac.LoanID = l.LoanID
    -- 终止条件：未达总期限且剩余本金>0
    WHERE ac.InstallmentNumber < l.LoanMonths 
      AND (ac.RemainingPrincipal - ac.PrincipalPayment) > 0
)
-- 输出最终摊销表
SELECT
    LoanID,
    InstallmentNumber,
    InstallmentDate,
    InitialPrincipal,
    MonthlyPayment,
    InterestPayment,
    PrincipalPayment,
    RemainingPrincipal
FROM AmortizationCTE
ORDER BY LoanID, InstallmentNumber;

关键适配说明 #

• 多贷款隔离：全程以LoanID作为关联标识，确保每笔贷款的摊销计算完全独立，不会出现数据交叉。
• 无函数依赖：所有计算逻辑内嵌在CTE中，不需要创建任何自定义函数，符合只读权限要求。
• 期限适配：通过LoanMonths控制递归终止，自动适配不同期限的贷款。
• 误差修正：最后一期直接将剩余本金作为当期还款额，解决四舍五入导致的尾差问题。

常见异常排查点 #

如果适配时出现数据错误，优先检查：

• 是否遗漏了LoanID的关联，导致不同贷款的摊销数据混淆。
• 年利率转月利率的公式是否正确（必须除以12再除以100，比如4.5%的月利率是4.5/12/100=0.00375）。
• 递归终止条件是否正确，避免期数超过LoanMonths。
• 日期计算逻辑是否符合你的还款规则（示例为每月固定日期，可按需调整DATEADD参数）。

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