如何使用NVL子句关联多个Select语句并处理无匹配记录场景

SELECT 
    q1.ITEM_NAME,
    NVL(q2."Last Trans Date", '无交易记录') AS "Last Trans Date",
    NVL(q3.total_on_hand, 0) AS total_on_hand_qty
FROM 
    -- 查询1作为主表，保留所有符合条件的商品记录
    (
        SELECT ic.ITEM_ID, ic.ITEM_NAME
        FROM ITEM_CBO ic 
        WHERE COLOR_DESC = 'BRAND'
    ) q1
LEFT JOIN
    -- 关联查询2的交易日期数据
    (
        SELECT 
            ptt.ITEM_ID, 
            TO_CHAR(MAX(DISTINCT(ptt.CREATE_DATE_TIME)), 'dd/mm/yyyy') as "Last Trans Date"
        FROM PROD_TRKG_TRAN ptt
        WHERE ptt.TRAN_TYPE = 500 
          AND ptt.TRAN_CODE = '010' -- 字符型数值建议加引号避免隐式转换，数值类型可去掉引号
          AND ptt.MENU_OPTN_NAME = 'LOA Pck Pick Cart'
        GROUP BY ptt.ITEM_ID
    ) q2 ON q1.ITEM_ID = q2.ITEM_ID
LEFT JOIN
    -- 关联查询3的库存汇总数据
    (
        SELECT 
            wi.ITEM_ID, 
            SUM(wi.ON_HAND_QTY) AS total_on_hand
        FROM WM_INVENTORY wi
        WHERE wi.ITEM_ID IN (SELECT ic.ITEM_ID from ITEM_CBO ic where COLOR_DESC = 'BRAND')
        GROUP BY wi.ITEM_ID
    ) q3 ON q1.ITEM_ID = q3.ITEM_ID;

逻辑说明 #

• 连接规则：使用LEFT JOIN关联三个子查询，以查询1的结果为主表，确保所有符合COLOR_DESC = 'BRAND'的商品记录都会保留，不会因为查询2、查询3无匹配数据被过滤
• NVL处理规则：
  • 当商品在查询2中无匹配交易记录时，Last Trans Date返回自定义的无交易记录提示，你也可以根据业务需求替换为NULL或者其他占位值
  • 当商品在查询3中无匹配库存记录时，库存汇总值返回0，避免后续数值统计时出现NULL运算错误

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