能否使用单条SQL从products等表获取带自定义列的多仓库库存数据

解决方案 #

可以通过多表关联 + 条件聚合的方式实现单条SQL查询，完全不需要循环遍历。

你原有的循环逻辑本质是先拉取全量SKU，再逐SKU匹配库存和仓库信息，直接把三层表做关联就能替代循环逻辑。
如果需要和你现有循环输出的明细数据完全对齐（每行对应一个SKU在单个仓库的库存明细），可以用以下SQL：

SELECT 
    p.id AS product_id,
    p.sku,
    ware.id AS warehouse_id,
    ware.name AS warehouse_name,
    ware_inv.available AS inventory,
    ware_inv.id AS inventory_id,
    ware.country
FROM products p
INNER JOIN warehouse_inventory AS ware_inv 
    ON p.sku = ware_inv.sku
INNER JOIN warehouses AS ware 
    ON ware.id = ware_inv.warehouse_id
WHERE ware.store_id = $1

如果要得到你截图中「每个SKU为一行，不同仓库库存作为自定义列」的最终效果，可以用条件聚合做行转列，SQL如下：

SELECT
    p.id AS product_id,
    p.sku,
    SUM(CASE WHEN ware.name = 'warehouse 1' THEN ware_inv.available ELSE 0 END) AS warehouse_1_inventory,
    SUM(CASE WHEN ware.name = 'warehouse 2' THEN ware_inv.available ELSE 0 END) AS warehouse_2_inventory,
    SUM(CASE WHEN ware.name = 'warehouse 3' THEN ware_inv.available ELSE 0 END) AS warehouse_3_inventory
    -- 其余仓库按上述格式补充即可
FROM products p
LEFT JOIN warehouse_inventory ware_inv ON p.sku = ware_inv.sku
LEFT JOIN warehouses ware 
    ON ware.id = ware_inv.warehouse_id 
    AND ware.store_id = $1
GROUP BY p.id, p.sku

如果某个SKU在对应仓库没有库存，会默认返回0，你也可以根据需求把ELSE 0改成ELSE NULL来显示空值。

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