基于FIFO方法从Voucher表扣减对应金额的实现方案咨询

针对按FIFO（先进先出，按代金券生成日期排序）规则从代金券表扣减使用金额的需求，可以通过窗口函数计算累计金额的方式实现，以下是具体的SQL解决方案（适配支持窗口函数的数据库如MySQL 8.0+、PostgreSQL等）：

原数据表结构与数据 #

Voucher表（修正拼写为标准的Voucher）：

Customer  Voucher  date       value    
A         VOC1     20/01/2023 200
A         VOC2     12/03/2023 350
A         VOC3     03/05/2023 200

ValueToRemove表：

Customer  Value
A         400

实现SQL代码 #

WITH VoucherWithRunningTotal AS (
    SELECT 
        v.*,
        -- 按客户分组、日期升序计算累计代金券金额（FIFO顺序）
        SUM(v.value) OVER (PARTITION BY v.Customer ORDER BY v.date ASC) AS running_total,
        -- 计算当前代金券之前的累计金额，用于判断扣减区间
        SUM(v.value) OVER (PARTITION BY v.Customer ORDER BY v.date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prev_running_total
    FROM Voucher v
),
CustomerRemoval AS (
    SELECT * FROM ValueToRemove
)
SELECT 
    v.Customer,
    v.Voucher,
    v.date,
    -- 计算每张代金券的剩余金额
    CASE
        -- 累计金额≤扣减总额，代金券全额扣完
        WHEN v.running_total <= cr.Value THEN 0
        -- 扣减金额落在当前代金券覆盖区间，计算剩余部分
        WHEN COALESCE(v.prev_running_total, 0) < cr.Value AND v.running_total > cr.Value THEN v.value - (cr.Value - COALESCE(v.prev_running_total, 0))
        -- 超出扣减总额，保留原金额
        ELSE v.value
    END AS value
FROM VoucherWithRunningTotal v
JOIN CustomerRemoval cr ON v.Customer = cr.Customer
ORDER BY v.Customer, v.date;

逻辑说明 #

1. 计算累计金额：通过窗口函数SUM() OVER (...)按客户分组、代金券生成日期升序排序，得到每张代金券的累计金额及之前的累计金额，明确FIFO的扣减顺序。
2. 判断扣减范围：
   • 若累计金额小于等于扣减总额，该代金券全额扣减，剩余为0；
   • 若扣减金额落在当前代金券的覆盖区间（之前累计金额 < 扣减金额 ≤ 当前累计金额），则计算剩余金额为原金额减去需扣减的剩余部分；
   • 超出扣减总额的代金券，保留原金额。
3. 关联扣减表：按客户关联两张表，确保每个客户的扣减逻辑独立执行。

执行结果 #

运行上述SQL后，将得到目标更新数据：

Customer  Voucher  date       value
A         VOC1     20/01/2023 0
A         VOC2     12/03/2023 150
A         VOC3     03/05/2023 200

如果需要直接更新Voucher表，可将查询结果转换为UPDATE语句（以MySQL为例）：

WITH VoucherWithRunningTotal AS (
    SELECT 
        v.*,
        SUM(v.value) OVER (PARTITION BY v.Customer ORDER BY v.date ASC) AS running_total,
        SUM(v.value) OVER (PARTITION BY v.Customer ORDER BY v.date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prev_running_total
    FROM Voucher v
),
CustomerRemoval AS (
    SELECT * FROM ValueToRemove
),
UpdatedVoucher AS (
    SELECT 
        v.Voucher,
        CASE
            WHEN v.running_total <= cr.Value THEN 0
            WHEN COALESCE(v.prev_running_total, 0) < cr.Value AND v.running_total > cr.Value THEN v.value - (cr.Value - COALESCE(v.prev_running_total, 0))
            ELSE v.value
        END AS new_value
    FROM VoucherWithRunningTotal v
    JOIN CustomerRemoval cr ON v.Customer = cr.Customer
)
UPDATE Voucher v
JOIN UpdatedVoucher uv ON v.Voucher = uv.Voucher
SET v.value = uv.new_value;

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