CTE正确使用方法求助：按业务规则生成拆分收款数据

嘿，我来帮你搞定这个CTE的问题！递归CTE刚好是处理这种「根据数值生成对应行数」场景的利器，下面直接上解决方案和详细解释：

WITH RecursiveApportionment AS (
    -- 锚点查询：获取基础数据并初始化第一行
    SELECT 
        MainRentAccountReference,
        EffectiveFromDate,
        CollectionDay,
        NumberOfCollections,
        DirectDebitTotalOverrideAmount,
        1 AS CurrentCollection,
        -- 生成第一期日期：用EffectiveFromDate的年月，替换日为CollectionDay
        DATEFROMPARTS(
            YEAR(EffectiveFromDate),
            MONTH(EffectiveFromDate),
            CollectionDay
        ) AS CollectionDate,
        -- 计算单期金额：总金额转成decimal后再除以次数，避免整数截断
        DirectDebitTotalOverrideAmount / CAST(NumberOfCollections AS DECIMAL(18,2)) AS InstallmentAmount
    FROM DirectDebitApportionment
    WHERE id = 1

    UNION ALL

    -- 递归部分：生成后续的分期行
    SELECT 
        MainRentAccountReference,
        EffectiveFromDate,
        CollectionDay,
        NumberOfCollections,
        DirectDebitTotalOverrideAmount,
        CurrentCollection + 1,
        -- 日期逐月递增
        DATEADD(MONTH, 1, CollectionDate),
        InstallmentAmount
    FROM RecursiveApportionment
    -- 终止条件：当前生成的行数还没达到总次数
    WHERE CurrentCollection < NumberOfCollections
)
-- 最终输出整理后的字段
SELECT 
    MainRentAccountReference,
    CollectionDate AS EffectiveFromDate,
    CollectionDay,
    NumberOfCollections,
    InstallmentAmount AS DirectDebitTotalOverrideAmount
FROM RecursiveApportionment
ORDER BY CollectionDate;

关键逻辑说明： #

• 锚点查询：先拿到id=1的原始数据，同时直接生成第一行的日期和单期金额，确保初始值正确。
• 递归迭代：每一次递归都会把当前行数加1，日期自动加一个月，金额保持单期值不变，直到生成的行数等于NumberOfCollections时停止。
• 细节处理：把NumberOfCollections转成DECIMAL是为了避免整数除法导致的金额截断问题，比如总金额是100、次数是3的话，能得到33.33而不是33。

小提醒： #

如果你的CollectionDay是31、30这类数值，遇到2月或者只有30天的月份时，SQL会自动把日期调整到当月最后一天（比如31/04/2018会变成30/04/2018），如果需要严格保留CollectionDay的数值，可能需要额外加一段逻辑处理这种边界情况。

你可以直接运行这段脚本，应该就能得到你想要的结果啦！

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