如何在Stock表与Request表中按指定逻辑查询可用库存数量

我来帮你搞定这个库存计算的问题！根据你的需求和给出的示例，我理解你想要实现的逻辑是：先算出库存总数量减去请求总数量的剩余值，然后优先保留Hold状态的库存，最终返回Available状态的剩余数量（或者说，当剩余总库存多于Available的数量时，就返回全部Available库存；如果剩余总库存比Available少，就返回这个剩余值）。

分步分析逻辑 #

1. 首先计算每个仓库、每个商品的总库存和Available状态库存；
2. 然后计算对应仓库、商品的总请求量；
3. 最后根据总剩余库存和Available库存的关系，返回对应的结果：
   • 如果总剩余库存 ≥ Available库存：返回Available的全部数量（因为Hold库存需要保留）
   • 如果总剩余库存 < Available库存：返回总剩余库存（此时Available库存已经被消耗了一部分，剩余的总库存就是可用量）

实现SQL查询 #

这里用CTE（公共表表达式）来拆分逻辑，让代码更清晰：

WITH StockSummary AS (
    SELECT 
        Warehouse,
        Item,
        SUM(Qty) AS TotalStock,
        SUM(CASE WHEN Status = 'Available' THEN Qty ELSE 0 END) AS AvailableQty
    FROM Stock
    GROUP BY Warehouse, Item
),
RequestSummary AS (
    SELECT 
        Warehouse,
        Item,
        SUM(Qty) AS TotalRequest
    FROM Request
    GROUP BY Warehouse, Item
)
SELECT 
    ss.Warehouse,
    ss.Item,
    CASE
        WHEN (ss.TotalStock - rs.TotalRequest) >= ss.AvailableQty THEN ss.AvailableQty
        ELSE (ss.TotalStock - rs.TotalRequest)
    END AS RemainingAvailableQty
FROM StockSummary ss
JOIN RequestSummary rs ON ss.Warehouse = rs.Warehouse AND ss.Item = rs.Item;

示例验证 #

• 当Request表中Pen的Qty是500时：
  TotalStock=200+800=1000，TotalRequest=500，剩余总库存=500。因为500≥200，所以返回200，完全符合你的示例。
• 补充说明：如果Request表中Pen的Qty是900时，剩余总库存=1000-900=100，此时100<200，会返回100——这应该是你描述中“变为600”的笔误？如果确实是Request Qty=600，剩余总库存=400≥200，会返回200，和你说的100不符，你可以根据实际需求调整CASE里的判断逻辑。

兼容无请求的情况 #

如果某些仓库+商品在Request表中没有记录（即没有请求），可以用LEFT JOIN并结合COALESCE处理NULL值：

WITH StockSummary AS (
    SELECT 
        Warehouse,
        Item,
        SUM(Qty) AS TotalStock,
        SUM(CASE WHEN Status = 'Available' THEN Qty ELSE 0 END) AS AvailableQty
    FROM Stock
    GROUP BY Warehouse, Item
),
RequestSummary AS (
    SELECT 
        Warehouse,
        Item,
        SUM(Qty) AS TotalRequest
    FROM Request
    GROUP BY Warehouse, Item
)
SELECT 
    ss.Warehouse,
    ss.Item,
    CASE
        WHEN (ss.TotalStock - COALESCE(rs.TotalRequest, 0)) >= ss.AvailableQty THEN ss.AvailableQty
        ELSE (ss.TotalStock - COALESCE(rs.TotalRequest, 0))
    END AS RemainingAvailableQty
FROM StockSummary ss
LEFT JOIN RequestSummary rs ON ss.Warehouse = rs.Warehouse AND ss.Item = rs.Item;

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