SQL Server中按顺序从资金表扣除现金流金额的实现

测试DDL与数据 #

首先定义测试用的表结构和数据，确保场景匹配：

CREATE TABLE Cashflow (
    CashflowID INT PRIMARY KEY,
    ProjectID INT,
    Amount DECIMAL(18,2),
    TransactionDate DATE
);

CREATE TABLE Funding (
    FundingID INT PRIMARY KEY,
    ProjectID INT,
    Amount DECIMAL(18,2),
    DepletionOrder INT
);

INSERT INTO Cashflow VALUES
(1, 1, 1500.00, '2024-01-01'),
(2, 1, 2500.00, '2024-02-01');

INSERT INTO Funding VALUES
(1, 1, 1000.00, 1),
(2, 1, 2000.00, 2),
(3, 1, 1500.00, 3);

修正后的递归CTE代码 #

核心是精准计算CashflowFunded字段，严格遵循资金项消耗顺序与剩余结转规则：

WITH OrderedCashflow AS (
    -- 按项目分组，给现金流按交易顺序生成序列
    SELECT 
        CashflowID,
        ProjectID,
        Amount,
        ROW_NUMBER() OVER (PARTITION BY ProjectID ORDER BY TransactionDate, CashflowID) AS CashflowSeq
    FROM Cashflow
),
OrderedFunding AS (
    -- 按项目分组，给资金项按消耗顺序生成序列
    SELECT 
        FundingID,
        ProjectID,
        Amount,
        DepletionOrder,
        ROW_NUMBER() OVER (PARTITION BY ProjectID ORDER BY DepletionOrder) AS FundingSeq
    FROM Funding
),
RecursiveAllocation AS (
    -- 锚点成员：初始化第一笔现金流与第一笔资金项的分摊
    SELECT
        oc.ProjectID,
        oc.CashflowID,
        ofd.FundingID,
        -- 首次分摊金额：取现金流总额与资金项总额的较小值
        CashflowFunded = MIN(oc.Amount, ofd.Amount),
        -- 剩余待分摊现金流
        CashflowRemainingToFund = oc.Amount - MIN(oc.Amount, ofd.Amount),
        -- 剩余资金项金额
        FundingRemaining = ofd.Amount - MIN(oc.Amount, ofd.Amount),
        oc.CashflowSeq,
        ofd.FundingSeq
    FROM OrderedCashflow oc
    JOIN OrderedFunding ofd ON oc.ProjectID = ofd.ProjectID
    WHERE oc.CashflowSeq = 1 AND ofd.FundingSeq = 1

    UNION ALL

    -- 递归成员：分三种场景处理后续分摊
    SELECT
        ra.ProjectID,
        -- 确定当前处理的现金流ID：若当前现金流未处理完则沿用，否则切换到下一笔
        CASE WHEN ra.CashflowRemainingToFund > 0 THEN ra.CashflowID ELSE oc.CashflowID END AS CashflowID,
        -- 确定当前使用的资金项ID：若当前资金未耗尽则沿用，否则切换到下一个
        CASE WHEN ra.FundingRemaining > 0 THEN ra.FundingID ELSE ofd.FundingID END AS FundingID,
        -- 核心：计算本次分摊金额
        CashflowFunded = CASE
            -- 场景1：当前现金流未处理完，当前资金项未耗尽
            WHEN ra.CashflowRemainingToFund > 0 AND ra.FundingRemaining > 0 THEN
                MIN(ra.CashflowRemainingToFund, ra.FundingRemaining)
            -- 场景2：当前现金流已处理完，切换到下一笔现金流，使用剩余资金项
            WHEN ra.CashflowRemainingToFund = 0 AND ra.FundingRemaining > 0 THEN
                MIN(oc.Amount, ra.FundingRemaining)
            -- 场景3：当前资金项已耗尽，切换到下一个资金项，处理剩余现金流
            WHEN ra.CashflowRemainingToFund > 0 AND ra.FundingRemaining = 0 THEN
                MIN(ra.CashflowRemainingToFund, ofd.Amount)
        END,
        -- 更新剩余待分摊现金流
        CashflowRemainingToFund = CASE
            WHEN ra.CashflowRemainingToFund > 0 AND ra.FundingRemaining > 0 THEN
                ra.CashflowRemainingToFund - MIN(ra.CashflowRemainingToFund, ra.FundingRemaining)
            WHEN ra.CashflowRemainingToFund = 0 AND ra.FundingRemaining > 0 THEN
                oc.Amount - MIN(oc.Amount, ra.FundingRemaining)
            WHEN ra.CashflowRemainingToFund > 0 AND ra.FundingRemaining = 0 THEN
                ra.CashflowRemainingToFund - MIN(ra.CashflowRemainingToFund, ofd.Amount)
        END,
        -- 更新剩余资金项金额
        FundingRemaining = CASE
            WHEN ra.CashflowRemainingToFund > 0 AND ra.FundingRemaining > 0 THEN
                ra.FundingRemaining - MIN(ra.CashflowRemainingToFund, ra.FundingRemaining)
            WHEN ra.CashflowRemainingToFund = 0 AND ra.FundingRemaining > 0 THEN
                ra.FundingRemaining - MIN(oc.Amount, ra.FundingRemaining)
            WHEN ra.CashflowRemainingToFund > 0 AND ra.FundingRemaining = 0 THEN
                ofd.Amount - MIN(ra.CashflowRemainingToFund, ofd.Amount)
        END,
        -- 更新现金流序列：当前现金流处理完则+1，否则沿用
        CASE WHEN ra.CashflowRemainingToFund > 0 THEN ra.CashflowSeq ELSE ra.CashflowSeq + 1 END AS CashflowSeq,
        -- 更新资金项序列：当前资金耗尽则+1，否则沿用
        CASE WHEN ra.FundingRemaining > 0 THEN ra.FundingSeq ELSE ra.FundingSeq + 1 END AS FundingSeq
    FROM RecursiveAllocation ra
    -- 关联下一笔现金流（仅当当前现金流已处理完时）
    LEFT JOIN OrderedCashflow oc ON ra.ProjectID = oc.ProjectID AND ra.CashflowSeq + 1 = oc.CashflowSeq
    -- 关联下一个资金项（仅当当前资金项已耗尽时）
    LEFT JOIN OrderedFunding ofd ON ra.ProjectID = ofd.ProjectID AND ra.FundingSeq + 1 = ofd.FundingSeq
    -- 终止条件：所有现金流处理完毕 或 所有资金项耗尽
    WHERE (ra.CashflowRemainingToFund > 0 OR oc.CashflowID IS NOT NULL)
      AND (ra.FundingRemaining > 0 OR ofd.FundingID IS NOT NULL)
)
-- 最终输出：过滤掉分摊金额为0的无效记录，按规则排序
SELECT
    ProjectID,
    CashflowID,
    FundingID,
    CashflowFunded,
    CashflowRemainingToFund,
    FundingRemaining
FROM RecursiveAllocation
WHERE CashflowFunded > 0
ORDER BY ProjectID, CashflowID, FundingID;

CashflowFunded计算逻辑说明 #

1. 锚点阶段：直接取第一笔现金流与第一笔资金项的较小值作为初始分摊金额，确保首次消耗符合规则。
2. 递归阶段：
   • 当现金流和资金项都有剩余时，取两者剩余值的较小值作为本次分摊金额。
   • 当现金流耗尽时，切换到下一笔现金流，用当前剩余资金项覆盖其部分/全部金额。
   • 当资金项耗尽时，切换到下一个资金项，覆盖当前现金流的剩余部分。

该方案完全基于递归CTE实现，避免了游标或循环带来的性能损耗，同时严格遵循资金项消耗顺序、剩余资金结转的业务规则。

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