SQL Server 2019中如何从总额逐行扣除金额并维护滚动余额？

需求说明 #

使用SQL Server 2019，需从指定总额中逐行扣除表内的金额数据，同时维护滚动余额，查询需返回两列：

• Adjusted：从总额中实际扣除的金额
• Balance：扣除后的剩余总额

规则示例：当总额为1000.00时，ID 1、2可全额扣除，ID 3仅能扣除剩余的600.00，ID 4因余额已耗尽无扣除金额：

TOTAL = 1000.00

    Id  Amount    Adjusted   Balance
    -----------------------------------------------
    1    150.00    150.00    850.00
    2    250.00    250.00    600.00
    3    700.00    600.00      0.00
    4    450.00      0.00      0.00

测试表脚本：

DECLARE @TotalAmount FLOAT=1000

DROP TABLE IF EXISTS [#MyTable];

SELECT Id, Amount, Adjusted, Balance 
INTO [#MyTable] 
FROM 
    (SELECT (1)Id,(150.00)Amount,(0.00)Adjusted,(0.00)Balance union
     SELECT (2)Id,(250.00)Amount,(0.00)Adjusted,(0.00)Balance union
     SELECT (3)Id,(700.00)Amount,(0.00)Adjusted,(0.00)Balance union
     SELECT (4)Id,(450.00)Amount,(0.00)Adjusted,(0.00)Balance
) [MyTable];

SELECT * FROM [#MyTable]

高效实现查询 #

利用窗口函数实现集合式计算，避免游标等行循环操作，性能最优：

DECLARE @TotalAmount FLOAT = 1000;

WITH RunningTotal AS (
    SELECT 
        Id,
        Amount,
        SUM(Amount) OVER(ORDER BY Id) AS CumulativeAmount
    FROM [#MyTable]
)
SELECT 
    Id,
    Amount,
    CASE 
        WHEN @TotalAmount - LAG(CumulativeAmount, 1, 0) OVER(ORDER BY Id) <= 0 THEN 0
        ELSE LEAST(Amount, @TotalAmount - LAG(CumulativeAmount, 1, 0) OVER(ORDER BY Id))
    END AS Adjusted,
    CASE 
        WHEN @TotalAmount - CumulativeAmount >= 0 THEN @TotalAmount - CumulativeAmount
        ELSE 0
    END AS Balance
FROM RunningTotal
ORDER BY Id;

逻辑解析 #

1. RunningTotal CTE：通过SUM(Amount) OVER(ORDER BY Id)计算每行及之前所有行的累计金额，明确当前行在扣除序列中的位置。
2. Adjusted列：用LAG函数获取上一行的累计金额，计算当前可用的剩余额度，取当前金额与剩余额度的较小值，若剩余额度为0则调整值为0。
3. Balance列：用总金额减去累计金额，结果为正则保留，否则置为0，确保余额不会出现负数。

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