基于SKU库存更新产品库存标签的SQL更新查询实现问询

场景说明 #

我有一张记录产品与对应SKU库存标签的表 product_skus_inventories，数据示例如下：

ProductID   SkuID   Inventory_Label
123         a1      InStock
123         a2      OutOfStock
123         a3      NULL

需要更新汇总表 product_summary，该表包含两个字段：

• product_id：产品ID
• inventory_label：库存汇总标签，可选值为 InStock、OutOfStock 或 Partial

更新规则 #

1. 若某产品下所有SKU的 Inventory_Label 均为 InStock 或 NULL，则汇总标签设为 InStock；
2. 若产品下至少存在一个SKU为 InStock，且同时存在其他不同值（如 OutOfStock），则设为 Partial；
3. 其余情况（无 InStock SKU，且存在至少一个 OutOfStock SKU）设为 OutOfStock。

实现方案 #

可以用单条UPDATE语句实现，核心思路是先通过子查询按产品ID聚合计算出对应的汇总标签，再关联汇总表完成更新。

MySQL 写法

UPDATE product_summary ps
JOIN (
    SELECT 
        ProductID,
        CASE
            -- 规则1：无任何OutOfStock的SKU，全为InStock或NULL
            WHEN SUM(CASE WHEN Inventory_Label = 'OutOfStock' THEN 1 ELSE 0 END) = 0 THEN 'InStock'
            -- 规则2：存在至少一个InStock，且存在OutOfStock（已被第一个分支排除无OutOfStock的情况）
            WHEN SUM(CASE WHEN Inventory_Label = 'InStock' THEN 1 ELSE 0 END) > 0 THEN 'Partial'
            -- 规则3：无InStock，且存在OutOfStock
            ELSE 'OutOfStock'
        END AS agg_inventory
    FROM product_skus_inventories
    GROUP BY ProductID
) agg ON ps.product_id = agg.ProductID
SET ps.inventory_label = agg.agg_inventory;

PostgreSQL 写法

UPDATE product_summary ps
SET inventory_label = agg.agg_inventory
FROM (
    SELECT 
        ProductID,
        CASE
            WHEN COUNT(CASE WHEN Inventory_Label = 'OutOfStock' THEN 1 END) = 0 THEN 'InStock'
            WHEN COUNT(CASE WHEN Inventory_Label = 'InStock' THEN 1 END) > 0 THEN 'Partial'
            ELSE 'OutOfStock'
        END AS agg_inventory
    FROM product_skus_inventories
    GROUP BY ProductID
) agg
WHERE ps.product_id = agg.ProductID;

逻辑说明 #

子查询通过分组统计每个产品下不同库存标签的数量：

• 第一个CASE分支：如果没有SKU是OutOfStock，说明所有SKU要么是InStock要么是NULL，符合规则1；
• 第二个CASE分支：在排除了无OutOfStock的情况后，只要存在至少一个InStock，就说明同时有InStock和OutOfStock，符合规则2；
• 剩余情况就是没有InStock但存在OutOfStock，符合规则3。

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