如何过滤UNION查询结果：排除特定条件的第二查询记录

安全阿岳

2026-6-16

问题描述

我有一个包含两个UNION ALL子查询的SQL语句，当前查询结果如下：

ITEM_NUMBER SUBINVENTORY_CODE   INV_LOCATOR     QUERY       IVT_SEQNUM  PRIORITY    OVERALL_PRIORITY
12923       41OPR               Z-B-01-02-C     1st Query   1           1           1
12923       41OPR               W-130-00-00-00  1st Query   2           1           2
12923       11OPR               AAAAAAA         2nd Query   1           2           1
12923       41OPR               AAAAAAA         2nd Query   1           2           1

需要排除的记录：第二查询中，与第一查询存在相同ITEM_NUMBER和SUBINVENTORY_CODE，且INV_LOCATOR值为'AAAAAAA'的记录。期望结果如下：

ITEM_NUMBER SUBINVENTORY_CODE   INV_LOCATOR     QUERY       IVT_SEQNUM  PRIORITY    OVERALL_PRIORITY
12923       41OPR               Z-B-01-02-C     1st Query   1           1           1
12923       41OPR               W-130-00-00-00  1st Query   2           1           2
12923       11OPR               AAAAAAA         2nd Query   1           2           1

尝试过的SQL代码

我尝试使用ROW_NUMBER分析窗口函数和cnt_first_query分析函数，但未能得到正确结果，当前代码如下：

SELECT * FROM
(SELECT item_number, subinventory_code, INV_LOCATOR, QUERY, ivt_seqnum, priority, ROW_NUMBER() OVER (PARTITION BY  priority, subinventory_code  ORDER BY  priority, subinventory_code  ) Overall_Priority 
  ,COUNT(CASE WHEN priority = 1 THEN 1 END) OVER (PARTITION BY item_number, subinventory_code) as cnt_first_query
  FROM
(SELECT ESI.item_number,  IOQD.subinventory_code  SUBINVENTORY_CODE, 

ROW_NUMBER() OVER (PARTITION BY ESI.item_number , IOQD.subinventory_code  
ORDER BY ESI.item_number, IOQD.subinventory_code ) ivt_seqnum
                
 ,LOC.SEGMENT1||'-'||LOC.SEGMENT2||'-'||LOC.SEGMENT3||'-'||LOC.SEGMENT4||'-'||LOC.SEGMENT5 INV_LOCATOR, 1 as priority, '1st Query' AS QUERY            --, ioqd.locator_id
 
 
 
FROM egp_system_items ESI, inv_onhand_quantities_detail IOQD, inv_item_locations LOC
WHERE ESI.INVENTORY_ITEM_ID = 100000110904412
AND ioqd.subinventory_code = '41OPR'
AND IOQD.inventory_item_id = ESI.inventory_item_id
and IOQD.organization_id = esi.organization_id
AND LOC.inventory_location_id = ioqd.locator_id

UNION ALL



SELECT ESI.item_number, decode(substr(IOQD.secondary_inventory,1,3),                                  
                                                              '11C','11CCL'
                                                              ,'11O','11OPR'
                                                              ,'18O','18OPR'
                                                              ,'41S','24000'
                                                              ,'41O','41OPR'
                                                              ,'60O','60OPR'
                                                              ,'70O','70OPR'
                                                              ,'70C','70CCL'
                                                              ,'70M', '70OPR'
                                                              ,IOQD.secondary_inventory) subinventory_code, 

ROW_NUMBER() OVER (PARTITION BY ESI.item_number , IOQD.secondary_inventory  
ORDER BY ESI.item_number, IOQD.secondary_inventory ) ivt_seqnum , 
                
case when 
 (SELECT iil.SEGMENT1||'-'||iil.SEGMENT2||'-'||iil.SEGMENT3||'-'||iil.SEGMENT4||'-'||iil.SEGMENT5
           FROM inv_item_loc_defaults iild, 
                inv_item_locations iil
          WHERE iild.inventory_item_id = esi.inventory_item_id
            AND iild.subinventory_code = IOQD.secondary_inventory
            AND iild.locator_id       = iil.inventory_location_id
            AND rownum= 1 and length(Segment1) >= 1 ) is not null 
then (SELECT iil.SEGMENT1||'-'||iil.SEGMENT2||'-'||iil.SEGMENT3||'-'||iil.SEGMENT4||'-'||iil.SEGMENT5
           FROM inv_item_loc_defaults iild, 
                inv_item_locations iil
          WHERE iild.inventory_item_id = esi.inventory_item_id
            AND iild.subinventory_code = IOQD.secondary_inventory
            AND iild.locator_id       = iil.inventory_location_id
            AND rownum= 1 and length(Segment1) >= 1 )   

when (SELECT iil.SEGMENT1||'-'||iil.SEGMENT2||'-'||iil.SEGMENT3||'-'||iil.SEGMENT4||'-'||iil.SEGMENT5
           FROM inv_secondary_locators iild, 
                inv_item_locations iil
          WHERE iild.inventory_item_id = esi.inventory_item_id
            AND iild.subinventory_code = IOQD.secondary_inventory
            AND iild.SECONDARY_LOCATOR= iil.inventory_location_id
            AND rownum = 1 and length(Segment1) >= 1 ) is not null 
        Then (SELECT iil.SEGMENT1||'-'||iil.SEGMENT2||'-'||iil.SEGMENT3||'-'||iil.SEGMENT4||'-'||iil.SEGMENT5
           FROM inv_secondary_locators iild, 
                inv_item_locations iil
          WHERE iild.inventory_item_id = esi.inventory_item_id
            AND iild.subinventory_code = IOQD.secondary_inventory
            AND iild.SECONDARY_LOCATOR= iil.inventory_location_id
            AND rownum = 1 and length(Segment1) >= 1 )

Else 'AAAAAAA'
END as      INV_LOCATOR   , 2 as priority  , '2nd Query' AS QUERY  
                
FROM egp_system_items ESI, INV_ITEM_SUB_INVENTORIES IOQD --,  inv_item_loc_defaults iild, inv_item_locations iil
WHERE IOQD.inventory_item_id = ESI.inventory_item_id
AND IOQD.organization_id = ESI.organization_id
AND ESI.INVENTORY_ITEM_ID = 100000110904412
--AND iild.inventory_item_id = esi.inventory_item_id
--AND iild.subinventory_code = IOQD.secondary_inventory
--AND iild.locator_id  = iil.inventory_location_id 

) --WHERE (priority <> 2 AND INV_LOCATOR <> 'AAAAAAA') 
)    --WHERE (INV_LOCATOR <> 'AAAAAAA' AND OVERALL_PRIORITY <=1)


WHERE NOT cnt_first_query > 0 AND INV_LOCATOR = 'AAAAAAA'

解决方案

原SQL的过滤条件逻辑错误，NOT cnt_first_query > 0会排除所有在第一查询中存在对应ITEM_NUMBER和SUBINVENTORY_CODE的记录，而非仅排除第二查询中符合条件的特定记录。以下是修正后的SQL：

SELECT item_number, subinventory_code, INV_LOCATOR, QUERY, ivt_seqnum, priority, Overall_Priority
FROM
(
    SELECT 
        item_number, 
        subinventory_code, 
        INV_LOCATOR, 
        QUERY, 
        ivt_seqnum, 
        priority, 
        ROW_NUMBER() OVER (PARTITION BY priority, subinventory_code ORDER BY priority, subinventory_code) Overall_Priority,
        -- 标记当前(item_number, subinventory_code)是否在第一查询中存在
        MAX(CASE WHEN priority = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY item_number, subinventory_code) AS exists_in_first_query
    FROM
    (
        -- 第一查询部分
        SELECT 
            ESI.item_number, 
            IOQD.subinventory_code SUBINVENTORY_CODE,
            ROW_NUMBER() OVER (PARTITION BY ESI.item_number, IOQD.subinventory_code ORDER BY ESI.item_number, IOQD.subinventory_code) ivt_seqnum,
            LOC.SEGMENT1||'-'||LOC.SEGMENT2||'-'||LOC.SEGMENT3||'-'||LOC.SEGMENT4||'-'||LOC.SEGMENT5 INV_LOCATOR, 
            1 as priority, 
            '1st Query' AS QUERY
        FROM egp_system_items ESI
        JOIN inv_onhand_quantities_detail IOQD ON IOQD.inventory_item_id = ESI.inventory_item_id AND IOQD.organization_id = esi.organization_id
        JOIN inv_item_locations LOC ON LOC.inventory_location_id = ioqd.locator_id
        WHERE ESI.INVENTORY_ITEM_ID = 100000110904412
          AND ioqd.subinventory_code = '41OPR'

        UNION ALL

        -- 第二查询部分
        SELECT 
            ESI.item_number, 
            decode(substr(IOQD.secondary_inventory,1,3),                                  
                '11C','11CCL',
                '11O','11OPR',
                '18O','18OPR',
                '41S','24000',
                '41O','41OPR',
                '60O','60OPR',
                '70O','70OPR',
                '70C','70CCL',
                '70M', '70OPR',
                IOQD.secondary_inventory) subinventory_code, 
            ROW_NUMBER() OVER (PARTITION BY ESI.item_number, IOQD.secondary_inventory ORDER BY ESI.item_number, IOQD.secondary_inventory) ivt_seqnum, 
            CASE 
                WHEN (
                    SELECT iil.SEGMENT1||'-'||iil.SEGMENT2||'-'||iil.SEGMENT3||'-'||iil.SEGMENT4||'-'||iil.SEGMENT5
                    FROM inv_item_loc_defaults iild
                    JOIN inv_item_locations iil ON iild.locator_id = iil.inventory_location_id
                    WHERE iild.inventory_item_id = esi.inventory_item_id
                      AND iild.subinventory_code = IOQD.secondary_inventory
                      AND length(iil.Segment1) >= 1
                      AND rownum=1
                ) IS NOT NULL 
                THEN (
                    SELECT iil.SEGMENT1||'-'||iil.SEGMENT2||'-'||iil.SEGMENT3||'-'||iil.SEGMENT4||'-'||iil.SEGMENT5
                    FROM inv_item_loc_defaults iild
                    JOIN inv_item_locations iil ON iild.locator_id = iil.inventory_location_id
                    WHERE iild.inventory_item_id = esi.inventory_item_id
                      AND iild.subinventory_code = IOQD.secondary_inventory
                      AND length(iil.Segment1) >= 1
                      AND rownum=1
                )
                WHEN (
                    SELECT iil.SEGMENT1||'-'||iil.SEGMENT2||'-'||iil.SEGMENT3||'-'||iil.SEGMENT4||'-'||iil.SEGMENT5
                    FROM inv_secondary_locators iild
                    JOIN inv_item_locations iil ON iild.SECONDARY_LOCATOR = iil.inventory_location_id
                    WHERE iild.inventory_item_id = esi.inventory_item_id
                      AND iild.subinventory_code = IOQD.secondary_inventory
                      AND length(iil.Segment1) >= 1
                      AND rownum=1
                ) IS NOT NULL
                THEN (
                    SELECT iil.SEGMENT1||'-'||iil.SEGMENT2||'-'||iil.SEGMENT3||'-'||iil.SEGMENT4||'-'||iil.SEGMENT5
                    FROM inv_secondary_locators iild
                    JOIN inv_item_locations iil ON iild.SECONDARY_LOCATOR = iil.inventory_location_id
                    WHERE iild.inventory_item_id = esi.inventory_item_id
                      AND iild.subinventory_code = IOQD.secondary_inventory
                      AND length(iil.Segment1) >= 1
                      AND rownum=1
                )
                ELSE 'AAAAAAA'
            END as INV_LOCATOR, 
            2 as priority, 
            '2nd Query' AS QUERY  
        FROM egp_system_items ESI
        JOIN INV_ITEM_SUB_INVENTORIES IOQD ON IOQD.inventory_item_id = ESI.inventory_item_id AND IOQD.organization_id = ESI.organization_id
        WHERE ESI.INVENTORY_ITEM_ID = 100000110904412
    ) t1
) t2
-- 精准排除不符合要求的记录
WHERE NOT (priority = 2 AND INV_LOCATOR = 'AAAAAAA' AND exists_in_first_query = 1);

关键修改点

用MAX(CASE WHEN priority=1 THEN 1 ELSE 0 END)窗口函数标记每个(item_number, subinventory_code)是否在第一查询中存在，替代原有的cnt_first_query。
外层过滤条件调整为NOT (priority = 2 AND INV_LOCATOR = 'AAAAAAA' AND exists_in_first_query = 1)，仅排除目标记录。
将隐式JOIN改为显式JOIN，提升代码可读性。

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

如何过滤UNION查询结果：排除特定条件的第二查询记录

关键修改点 #

关键修改点