Oracle SQL：如何将含子查询的语句与Group By查询合并？

架构老林

2026-6-16

合并后的Oracle SQL查询

直接把第一个查询中的标量子查询加入到第二个查询的SELECT列表即可，因为该子查询是基于IC.ITEM_ID的聚合计算，每组数据对应唯一的ITEM_ID，不需要将其加入GROUP BY子句。同时建议统一使用现代JOIN语法替代旧的逗号连接，提升可读性：

SELECT
    LH.AISLE AS "Aisle",
    LH.DSP_LOCN AS "Location",
    WI.TC_LPN_ID AS "iLPN",
    IC.ITEM_NAME AS "Item",
    WI.ON_HAND_QTY AS "Qty",
    -- 保留第一个查询的标量子查询
    (SELECT
        SUM(OLI.ORDER_QTY)
     FROM ORDERS O
     JOIN ORDER_LINE_ITEM OLI ON O.ORDER_ID = OLI.ORDER_ID
     WHERE O.DO_STATUS = 110
       AND OLI.ITEM_ID = IC.ITEM_ID) AS "Released Qty",
    -- 保留第二个查询的分组求和
    SUM(WI2.ON_HAND_QTY) AS "CP Qty"
FROM LOCN_HDR LH
JOIN WM_INVENTORY WI ON WI.LOCATION_ID = LH.LOCN_ID
JOIN ITEM_CBO IC ON WI.ITEM_ID = IC.ITEM_ID
LEFT JOIN WM_INVENTORY WI2 ON WI2.ITEM_ID = IC.ITEM_ID AND WI2.LOCN_CLASS = 'C'
WHERE LH.ZONE = 'R'
  AND LH.LVL = '20'
GROUP BY
    LH.AISLE,
    LH.DSP_LOCN,
    WI.TC_LPN_ID,
    IC.ITEM_NAME,
    WI.ON_HAND_QTY
ORDER BY LH.DSP_LOCN;