PostgreSQL中跨表针对指定仓库批量扣除库存值的实现问询

针对你提出的需求——仅对Warehouse为A的库存记录，按对应ProductID的Sold数量依次扣减并计算可用库存，我提供以下可行的PostgreSQL实现方案：

核心思路 #

我们需要按ProductID和Warehouse='A'分组，对库存记录按顺序累计库存，然后逐步抵扣销售数量，直到销售数量耗尽；Warehouse为B的记录则直接保留原库存作为可用量。

完整SQL代码 #

WITH inventory_with_sold AS (
    -- 关联库存表和销售表，获取每个产品对应的销售数量
    SELECT
        i.ProductID,
        i.Warehouse,
        i.Locator,
        i.qtyOnHand,
        COALESCE(s.Sold, 0) AS total_sold
    FROM inventory i
    LEFT JOIN sales s ON i.ProductID = s.ProductID
),
inventory_with_running_total AS (
    -- 计算每个仓库下产品的累计库存（按Locator排序，可根据实际需求调整排序字段）
    SELECT
        *,
        SUM(qtyOnHand) OVER (
            PARTITION BY ProductID, Warehouse
            ORDER BY Locator
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS running_total,
        -- 获取上一条记录的累计库存，用于计算当前记录需要抵扣的数量
        LAG(SUM(qtyOnHand) OVER (
            PARTITION BY ProductID, Warehouse
            ORDER BY Locator
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ), 1, 0) OVER (
            PARTITION BY ProductID, Warehouse
            ORDER BY Locator
        ) AS prev_running_total
    FROM inventory_with_sold
)
SELECT
    ProductID,
    Warehouse,
    Locator,
    qtyOnHand,
    CASE
        WHEN Warehouse != 'A' THEN qtyOnHand
        ELSE
            GREATEST(
                0,
                qtyOnHand - GREATEST(0, total_sold - prev_running_total)
            )
        END AS available
FROM inventory_with_running_total
ORDER BY ProductID, Warehouse, Locator;

代码解释 #

1. inventory_with_sold CTE：通过LEFT JOIN关联库存表和销售表，用COALESCE确保没有销售记录的产品total_sold为0，避免空值影响计算。
2. inventory_with_running_total CTE：
   • 使用窗口函数SUM()计算每个ProductID+Warehouse分组内的累计库存，排序字段用Locator，你可以根据实际业务调整排序逻辑（比如入库时间等）。
   • 用LAG()函数获取上一条记录的累计库存，这样我们能知道当前记录需要承担多少抵扣量。
3. 最终SELECT：
   • 对Warehouse不是A的记录，直接返回原库存作为available。
   • 对Warehouse为A的记录，先计算还未抵扣的销售数量（total_sold - prev_running_total），如果这个值大于0，就从当前库存中扣除，最终结果不能小于0（用GREATEST(0, ...)保证）。

验证结果 #

执行上述SQL后，会得到你期望的结果：

ProductID | Warehouse | Locator | qtyOnHand | available #

100 | A | 123 | 12 | 0
100 | A | 124 | 12 | 0
100 | A | 124 | 8 | 6
101 | A | 126 | 6 | 0
101 | B | 127 | 12 | 12

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