如何实现关联贷方的借方余额变动日志（SQL方案）

完全可以用CTE结合窗口函数实现这个需求，不需要逐行循环或复杂的IF判断。核心思路是先按交易日期排序，计算累计的贷方可用余额和借方待抵扣金额，再通过层级关联匹配每笔借方对应的贷方记录。

测试数据准备 #

首先创建测试用的TransactionLog表并插入数据：

CREATE TABLE TransactionLog (
    TransactionID INT PRIMARY KEY,
    TransactionDate DATE,
    TransactionType VARCHAR(10), -- 'Credit' 贷方, 'Debit' 借方
    Amount DECIMAL(18,2)
);

INSERT INTO TransactionLog VALUES
(1, '2024-01-01', 'Credit', 100.00),
(2, '2024-01-02', 'Credit', 150.00),
(3, '2024-01-03', 'Debit', 120.00),
(4, '2024-01-04', 'Debit', 110.00),
(5, '2024-01-05', 'Credit', 80.00),
(6, '2024-01-06', 'Debit', 90.00);

核心实现SQL #

下面的SQL通过多层CTE实现抵扣逻辑，最终生成OutputDebitLogs表：

WITH SortedTransactions AS (
    -- 按交易日期+ID排序，给每笔交易分配序号，确保处理顺序唯一
    SELECT 
        TransactionID,
        TransactionDate,
        TransactionType,
        Amount,
        ROW_NUMBER() OVER (ORDER BY TransactionDate, TransactionID) AS Seq
    FROM TransactionLog
),
CreditBalances AS (
    -- 提取贷方交易，记录初始剩余余额
    SELECT 
        TransactionID AS CreditID,
        TransactionDate AS CreditDate,
        Amount AS CreditAmount,
        Amount AS RemainingCredit,
        Seq AS CreditSeq
    FROM SortedTransactions
    WHERE TransactionType = 'Credit'
),
DebitRequests AS (
    -- 提取借方交易，计算累计待抵扣总额
    SELECT 
        TransactionID AS DebitID,
        TransactionDate AS DebitDate,
        Amount AS DebitAmount,
        SUM(Amount) OVER (ORDER BY Seq ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumulativeDebit
    FROM SortedTransactions
    WHERE TransactionType = 'Debit'
),
CumulativeCredits AS (
    -- 计算贷方的累计余额，用于匹配借方的累计抵扣需求
    SELECT 
        CreditID,
        CreditDate,
        CreditAmount,
        RemainingCredit,
        SUM(CreditAmount) OVER (ORDER BY CreditSeq) AS CumulativeCredit
    FROM CreditBalances
),
DebitCreditMatches AS (
    -- 匹配借方与贷方，计算每笔贷方对当前借方的抵扣金额
    SELECT 
        d.DebitID,
        d.DebitDate,
        d.DebitAmount,
        c.CreditID,
        c.CreditDate,
        -- 计算实际抵扣金额
        CASE 
            -- 借方累计需求未覆盖当前贷方的起始累计余额，无抵扣
            WHEN d.CumulativeDebit <= c.CumulativeCredit - c.RemainingCredit THEN 0
            -- 借方累计需求已超过当前贷方的结束累计余额，全额抵扣剩余贷方
            WHEN d.CumulativeDebit - d.DebitAmount >= c.CumulativeCredit THEN c.RemainingCredit
            -- 部分抵扣：借方需求覆盖当前贷方的部分区间
            ELSE 
                LEAST(d.CumulativeDebit, c.CumulativeCredit) - GREATEST(d.CumulativeDebit - d.DebitAmount, c.CumulativeCredit - c.RemainingCredit)
        END AS DeductedAmount
    FROM DebitRequests d
    CROSS JOIN CumulativeCredits c
    -- 只保留有抵扣交集的记录
    WHERE c.CumulativeCredit > d.CumulativeDebit - d.DebitAmount
      AND c.CumulativeCredit - c.RemainingCredit < d.CumulativeDebit
)
-- 生成最终结果表，过滤无抵扣的记录
SELECT 
    DebitID,
    DebitDate,
    DebitAmount,
    CreditID,
    CreditDate,
    DeductedAmount
INTO OutputDebitLogs
FROM DebitCreditMatches
WHERE DeductedAmount > 0
ORDER BY DebitDate, DebitID, CreditDate;

逻辑说明 #

1. SortedTransactions：统一排序规则，确保交易按时间顺序处理，避免日期相同的交易顺序混乱。
2. CreditBalances：单独提取贷方交易，记录每笔贷方的初始剩余金额。
3. DebitRequests：提取借方交易并计算累计待抵扣金额，用于定位需要匹配的贷方区间。
4. CumulativeCredits：计算贷方的累计余额，明确每笔贷方在整体余额中的位置范围。
5. DebitCreditMatches：通过累计值的区间匹配，精准计算每笔贷方对当前借方的抵扣金额，排除无关联的记录。
6. 最后将有效抵扣结果存入OutputDebitLogs表。

期望输出验证 #

针对测试数据，最终OutputDebitLogs的结果如下：

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