如何基于优先级对stock表多行库存数量进行扣减计算？

这是个很常见的按优先级分配库存扣减的场景，用窗口函数就能干净利落地解决，我给你拆解下具体思路和代码：

要实现按优先级依次扣减，关键是先算出每个仓库在优先级序列里的累计可用量，再根据这个累计值判断当前仓库需要承担多少扣减：

• 先按SKU分组、优先级排序，计算从最高优先级到当前仓库的累计库存
• 对比累计库存和需要扣减的总量，确定每个仓库的实际扣减数量
• 最后用计算出的扣减量更新原表的库存

假设我们要扣减的总量是4，下面以支持窗口函数的数据库（比如PostgreSQL、MySQL 8.0+、SQL Server等）为例：

通用版（PostgreSQL/SQL Server） #

-- 定义要扣减的总量
DECLARE @deduct_qty INT = 4;

WITH stock_with_running_total AS (
    SELECT 
        Id, SKU, priority, Warehouse, Qty,
        -- 计算从最高优先级到当前行的累计库存
        SUM(Qty) OVER (PARTITION BY SKU ORDER BY priority) AS running_total,
        -- 计算当前行之前的累计库存（第一行用0替代NULL）
        COALESCE(SUM(Qty) OVER (PARTITION BY SKU ORDER BY priority ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS prev_running_total
    FROM stock
    -- 如果只处理特定SKU可以加WHERE SKU = 'sku1'，否则处理所有SKU
),
deduct_calculation AS (
    SELECT 
        Id,
        CASE
            -- 累计库存还没到扣减量，全扣
            WHEN running_total <= @deduct_qty THEN Qty
            -- 前面的累计已经覆盖了扣减量，不扣
            WHEN prev_running_total >= @deduct_qty THEN 0
            -- 扣减剩余的部分
            ELSE @deduct_qty - prev_running_total
        END AS deduct_amount
    FROM stock_with_running_total
)
-- 更新原表库存
UPDATE stock s
SET Qty = s.Qty - dc.deduct_amount
FROM deduct_calculation dc
WHERE s.Id = dc.Id;

MySQL 适配版 #

MySQL的UPDATE JOIN语法略有不同，调整后的代码如下：

SET @deduct_qty = 4;

WITH stock_with_running_total AS (
    SELECT 
        Id, SKU, priority, Warehouse, Qty,
        SUM(Qty) OVER (PARTITION BY SKU ORDER BY priority) AS running_total,
        COALESCE(SUM(Qty) OVER (PARTITION BY SKU ORDER BY priority ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS prev_running_total
    FROM stock
    -- 可选：WHERE SKU = 'sku1'
),
deduct_calculation AS (
    SELECT 
        Id,
        CASE
            WHEN running_total <= @deduct_qty THEN Qty
            WHEN prev_running_total >= @deduct_qty THEN 0
            ELSE @deduct_qty - prev_running_total
        END AS deduct_amount
    FROM stock_with_running_total
)
UPDATE stock s
JOIN deduct_calculation dc ON s.Id = dc.Id
SET s.Qty = s.Qty - dc.deduct_amount;

拿你的示例数据来说：

1. running_total列计算结果分别是1、3、6（对应p1到p3的累计库存）
2. prev_running_total列结果是0、1、3
3. 扣减逻辑：
   • p1仓库：累计1≤4，全扣1，库存变为0
   • p2仓库：累计3≤4，全扣2，库存变为0
   • p3仓库：前面累计3<4，扣减4-3=1，库存从3变为2，完全符合你的预期输出

如果需要批量处理多个SKU的扣减，只需要去掉WHERE SKU = 'sku1'，并且可以把固定的@deduct_qty改成关联一个扣减需求表（比如每个SKU对应不同的扣减量）。

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