SQL Server中按规则用Cash抵扣Credit计算现金流的实现

在SQL Server中，要实现同客户的Cash按日期顺序抵扣未结清Credit的需求，递归CTE是最适合的方案——它能逐行处理交易，持续维护未结清的Credit余额，解决LAG/LEAD无法完成累计计算的问题。

实现步骤 #

1. 过滤出仅包含Credit和Cash的交易，按客户分组、日期排序，给每笔交易分配唯一序号，确保处理顺序稳定。
2. 通过递归CTE逐行计算：
   • 遇到Credit交易，直接累加未结清余额；
   • 遇到Cash交易，用Cash金额抵扣当前未结清余额，余额不能为负，同时记录未使用的Cash部分。

完整SQL代码（可直接创建视图） #

假设你的交易表名为Transactions，字段为Date, Name, Type, Amount，代码如下：

CREATE VIEW CustomerCashFlow AS
WITH SortedTransactions AS (
    -- 过滤目标交易类型，按客户+日期排序并编号
    SELECT 
        Date,
        Name,
        Type,
        Amount,
        ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Date) AS RowNum
    FROM Transactions
    WHERE Type IN ('Credit', 'Cash')
),
RecursiveBalance AS (
    -- 递归基础：每个客户的第一笔交易
    SELECT 
        Date,
        Name,
        Type,
        Amount,
        RowNum,
        -- 初始化未结清余额：第一笔是Credit则为金额，Cash则为0
        CASE WHEN Type = 'Credit' THEN Amount ELSE 0 END AS OpenBalance,
        -- 初始化未使用Cash：第一笔是Cash则为金额，Credit则为0
        CASE WHEN Type = 'Cash' THEN Amount ELSE 0 END AS UnusedCash
    FROM SortedTransactions
    WHERE RowNum = 1

    UNION ALL

    -- 递归处理后续交易
    SELECT 
        st.Date,
        st.Name,
        st.Type,
        st.Amount,
        st.RowNum,
        -- 更新未结清余额
        CASE 
            WHEN st.Type = 'Credit' THEN rb.OpenBalance + st.Amount
            ELSE IIF(rb.OpenBalance - st.Amount < 0, 0, rb.OpenBalance - st.Amount)
        END AS OpenBalance,
        -- 更新未使用Cash
        CASE 
            WHEN st.Type = 'Cash' THEN IIF(st.Amount - rb.OpenBalance < 0, 0, st.Amount - rb.OpenBalance)
            ELSE 0
        END AS UnusedCash
    FROM SortedTransactions st
    INNER JOIN RecursiveBalance rb 
        ON st.Name = rb.Name AND st.RowNum = rb.RowNum + 1
)
-- 最终输出：包含每笔交易的抵扣详情
SELECT 
    Date,
    Name,
    Type,
    Amount,
    OpenBalance AS CurrentOutstandingCredit, -- 当前未结清Credit余额
    UnusedCash, -- Cash未使用的部分（无对应Credit可抵扣）
    Amount - UnusedCash AS AmountAppliedToCredit -- Cash实际抵扣的Credit金额
FROM RecursiveBalance;

关键说明 #

• 如果同一客户同一日期有多笔交易，建议在ORDER BY Date后添加额外排序字段（如交易ID），避免排序不稳定：ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Date, TransactionID) AS RowNum
• 递归CTE会逐行传递未结清余额，确保每笔Cash都按顺序抵扣最早的未结清Credit
• 视图会自动同步源表数据，每次查询都会重新计算最新的抵扣状态

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