如何通过SQL按指定批次扣减商品库存？现有SQL执行异常求助

现有ProductIn表中，Barcode为11的Coke有两条库存记录：

• 入库日期2024-01-01、过期日期2025-03-10，库存数量20
• 入库日期2024-02-06、过期日期2026-01-05，库存数量50

需要实现先进先出的扣减逻辑：从第一条记录扣减20（剩余0），第二条记录扣减5（剩余45），总计扣减25。

当前执行的错误SQL会给所有Barcode=11的记录统一扣减25，导致第一条记录数量变为-5，第二条变为25，完全不符合需求。
错误SQL：

update ProductIn
set Quantity=Quantity-25
where Barcode=11    

核心逻辑是按入库时间排序，依次扣减直到满足总扣减量25，以下是不同数据库环境下的实现方案：

方案1：MySQL 8.0+（支持窗口函数与CTE） #

通过CTE计算每条记录的应扣减数量，再执行更新：

WITH inventory AS (
    SELECT 
        *,
        SUM(Quantity) OVER (ORDER BY InDate) AS cumulative_qty,
        25 AS total_deduct
    FROM ProductIn
    WHERE Barcode = 11
    ORDER BY InDate
),
deduct_calc AS (
    SELECT
        *,
        CASE
            WHEN cumulative_qty - Quantity < total_deduct THEN Quantity
            ELSE total_deduct - (cumulative_qty - Quantity)
        END AS deduct_amount
    FROM inventory
    WHERE cumulative_qty - Quantity < total_deduct
)
UPDATE ProductIn pi
JOIN deduct_calc dc ON pi.id = dc.id -- 假设表存在唯一主键id
SET pi.Quantity = pi.Quantity - dc.deduct_amount;

方案2：SQL Server #

利用CTE计算扣减量后执行更新：

WITH inventory AS (
    SELECT 
        *,
        SUM(Quantity) OVER (ORDER BY InDate ROWS UNBOUNDED PRECEDING) AS cumulative_qty
    FROM ProductIn
    WHERE Barcode = 11
),
deduct_calc AS (
    SELECT
        *,
        CASE
            WHEN cumulative_qty - Quantity < 25 THEN Quantity
            ELSE 25 - (cumulative_qty - Quantity)
        END AS deduct_amount
    FROM inventory
    WHERE cumulative_qty - Quantity < 25
)
UPDATE pi
SET pi.Quantity = pi.Quantity - dc.deduct_amount
FROM ProductIn pi
JOIN deduct_calc dc ON pi.id = dc.id;

方案3：通用分步更新（适配无窗口函数的数据库） #

如果数据库不支持窗口函数，可分两次原子更新：

1. 扣减第一条记录的全部20：

-- MySQL用LIMIT，SQL Server用TOP 1，PostgreSQL用LIMIT 1
UPDATE ProductIn
SET Quantity = Quantity - 20
WHERE Barcode = 11
ORDER BY InDate
LIMIT 1;

2. 从第二条记录扣减剩余的5：

UPDATE ProductIn
SET Quantity = Quantity - 5
WHERE Barcode = 11
AND Quantity > 0
ORDER BY InDate
LIMIT 1;

注意：需将两次更新放在同一事务中，避免中途异常导致数据不一致。

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