如何修正SQL查询，合并同表两仓库的QtyOnHand数据？

需求说明 #

从InvWarehouse表提取数据，将OF仓库和01仓库的QtyOnHand合并到同一行，Warehouse列仅显示OF，实现两仓库库存数据的对比。

原始查询与数据 #

原始查询语句 #

select Warehouse
, StockCode
, QtyOnHand
, QtyAllocated
, SalesQty1
, SalesQty2
, SalesQty3
from InvWarehouse

原始查询返回数据 #

期望结果 #

错误查询分析 #

你编写的查询存在两个核心问题：

1. 连接条件错误：用WOF.Warehouse = W01.Warehouse关联，无法匹配同一物料的两个仓库记录
2. 过滤条件逻辑错误：where WOF.Warehouse = 'OF'将左连接转为内连接，丢失OF仓库独有的物料

错误查询代码：

select
          WOF.Warehouse
        , WOF.StockCode
        , WOF.QtyOnHand
        , W01.QtyOnHand
        , W01.QtyAllocated
        , W01.[SalesQty1]
        , W01.[SalesQty2]
        , W01.[SalesQty3]
    from InvWarehouse W01
    left join InvWarehouse WOF on WOF.Warehouse = W01.Warehouse
    where W01.QtyOnHand > '0'
    and WOF.Warehouse = 'OF'

修正后的查询 #

方法1：使用LEFT JOIN关联物料 #

以OF仓库为主表，通过StockCode关联01仓库的同物料记录，确保保留所有OF仓库的物料：

select
    WOF.Warehouse,
    WOF.StockCode,
    W01.QtyOnHand as [W01.QtyOnHand],
    WOF.QtyOnHand as [WOF.QtyOnHand],
    W01.QtyAllocated as [W01.QtyAllocated],
    WOF.SalesQty1 as [WOF.SalesQty1],
    WOF.SalesQty2 as [WOF.SalesQty2],
    WOF.SalesQty3 as [WOF.SalesQty3]
from InvWarehouse WOF
left join InvWarehouse W01 
    on WOF.StockCode = W01.StockCode 
    and W01.Warehouse = '01'
where WOF.Warehouse = 'OF'

方法2：使用条件聚合（适合多仓库扩展） #

通过分组StockCode，用CASE WHEN提取不同仓库的字段值：

select
    'OF' as Warehouse,
    StockCode,
    MAX(CASE WHEN Warehouse = '01' THEN QtyOnHand END) as [W01.QtyOnHand],
    MAX(CASE WHEN Warehouse = 'OF' THEN QtyOnHand END) as [WOF.QtyOnHand],
    MAX(CASE WHEN Warehouse = '01' THEN QtyAllocated END) as [W01.QtyAllocated],
    MAX(CASE WHEN Warehouse = 'OF' THEN SalesQty1 END) as [WOF.SalesQty1],
    MAX(CASE WHEN Warehouse = 'OF' THEN SalesQty2 END) as [WOF.SalesQty2],
    MAX(CASE WHEN Warehouse = 'OF' THEN SalesQty3 END) as [WOF.SalesQty3]
from InvWarehouse
where Warehouse in ('OF', '01')
group by StockCode

修正说明 #

• 方法1：以OF仓库为基础，左连接01仓库的同物料记录，OF独有的物料不会丢失，01仓库不存在的字段显示为NULL
• 方法2：通过聚合函数匹配不同仓库的字段，扩展性更强，后续新增仓库只需添加对应CASE WHEN语句
• 列别名严格匹配期望的结果格式

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