基于日期从旧到新扣减MySQL表中指定商品的库存数量

问题描述 #

现有表ARTICOLISCADENZA，结构如下：

CREATE TABLE ARTICOLISCADENZA(CODARTICOLO int NOT NULL,
QUANTITA INT,
DATA DATE,
LOTTO VARCHAR(200),
FOREIGN KEY(CODARTICOLO) REFERENCES ARTICOLIDETT(CODARTICOLO)
ON DELETE CASCADE
ON UPDATE CASCADE
);

需求为：针对指定CODARTICOLO，按DATA从旧到新的顺序扣减QUANTITA。例如扣减CODARTICOLO=1503的5个数量时，先从DATA='2025-01-10'的记录扣4个，再从DATA='2025-02-10'的记录扣1个。

解决方案 #

利用MySQL用户变量跟踪剩余待扣减数量，结合带排序的UPDATE语句实现按顺序扣减：

SET @remaining = 5; -- 设置需要扣减的总数量
SET @target_cod = 1503; -- 设置目标商品编码

UPDATE ARTICOLISCADENZA
SET QUANTITA = CASE
    WHEN @remaining >= QUANTITA THEN
        (@remaining := @remaining - QUANTITA) * 0 -- 扣完当前记录库存，更新剩余待扣量
    ELSE
        QUANTITA - @remaining * (@remaining := 0) -- 扣减剩余部分，将剩余待扣量置0
END
WHERE CODARTICOLO = @target_cod
AND QUANTITA > 0
AND @remaining > 0
ORDER BY DATA ASC;

逻辑说明 #

1. 初始化两个变量：@remaining为需要扣减的总数量，@target_cod为目标商品编码；
2. 按DATA升序（旧到新）筛选目标商品的有效库存记录（QUANTITA>0）；
3. 逐条处理记录：
   • 若剩余待扣量大于等于当前记录库存，将当前记录库存置0，同时更新剩余待扣量；
   • 若剩余待扣量小于当前记录库存，从当前记录扣减剩余量，随后将剩余待扣量置0，终止后续处理；
4. 当@remaining变为0时，后续记录不再执行扣减操作。

注意事项 #

• 执行前建议开启事务，避免中途出错导致数据不一致；
• 若需集成到业务逻辑中，可将变量替换为参数，或封装为存储过程复用。

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