更新除某些列上具有相同值的行之外的所有行

Question

Nick

Asked: 2024-11-17 03:57:16 +0800 CST2024-11-17 03:57:16 +0800 CST 2024-11-17 03:57:16 +0800 CST

对从 UNION 查询中获取的数据进行优先排序/过滤

772

我有以下查询中的以下数据样本，其中我有两个UNIONed 查询。我想从总体查询中排除ITEM_NUMBER在第二个查询中存在与SUBINVENTORY_CODE第一个查询中相同的记录，并且INV_LOCATOR第二个查询中的记录的值为“AAAAAAA”。我曾尝试通过使用分析窗口函数来实现这一点ROW_NUMBER，但我承认我没有看到我需要如何更改它以ROW_NUMBER适当地设置值，然后在最外层的查询中对其进行过滤。

这是我基于以下查询的当前结果：

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     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

正如您在所需结果中看到的，我们从“第二个查询”行中排除了最后一条记录，因为它包含ANDINV_LOCATOR的值，并且此相同和组合已经存在于“第一个查询”的结果集中AAAAAAA ITEM_NUMBERSUBINVENTORY_CODE

SELECT * FROM
(SELECT item_number, subinventory_code, INV_LOCATOR, QUERY, ivt_seqnum, priority, ROW_NUMBER() OVER (PARTITION BY priority, item_number, 
subinventory_code  ORDER BY priority DESC, priority) Overall_Priority 
  
  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, 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 LOC.inventory_location_id = ioqd.locator_id

UNION ALL
      
SELECT ESI.item_number, decode(substr(IOQD.secondary_inventory,1,3),                                
                                                              
                                                              ,'41O','41OPR'                                                                  
                                                              ,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 )     
 
Else 'AAAAAAA'
END as      INV_LOCATOR   , 2 as priority  , '2nd Query' AS QUERY       
               
FROM egp_system_items ESI, INV_ITEM_SUB_INVENTORIES IOQD 
WHERE IOQD.inventory_item_id = ESI.inventory_item_id    
AND ESI.INVENTORY_ITEM_ID = 100000110904412

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

编辑

我添加了名为 cnt_first_query 的附加分析函数，并尝试在外部查询中对其进行过滤，但现在它仅获取 1 行，即下面的第 3 行“11OPR”和“AAAAAAA”

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'

2 个回答

Voted

TGDEV · Answer 1 · 2024-11-17T04:07:07+08:00

为了获得所需的结果，您可以使用通用表表达式 (CTE) 来分隔第一个查询和第二个查询，这样您就可以在第二个查询中筛选记录时引用第一个查询。以下是修改 SQL 查询的方法：

WITH 
First_Query AS (
    SELECT ESI.item_number, IOQD.subinventory_code AS subinventory_code, 
        ROW_NUMBER() OVER (PARTITION BY ESI.item_number, IOQD.subinventory_code ORDER BY ESI.item_number, IOQD.subinventory_code) AS ivt_seqnum,
        LOC.SEGMENT1 || '-' || LOC.SEGMENT2 || '-' || LOC.SEGMENT3 || '-' || LOC.SEGMENT4 || '-' || LOC.SEGMENT5 AS 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
    JOIN inv_item_locations LOC ON LOC.inventory_location_id = IOQD.locator_id
    WHERE ESI.INVENTORY_ITEM_ID = 100000110904412
      AND IOQD.subinventory_code = '41OPR'
),
Second_Query AS (
    SELECT ESI.item_number,
        DECODE(SUBSTR(IOQD.secondary_inventory, 1, 3), '41O', '41OPR', IOQD.secondary_inventory) AS subinventory_code,
        ROW_NUMBER() OVER (PARTITION BY ESI.item_number, IOQD.secondary_inventory ORDER BY ESI.item_number, IOQD.secondary_inventory) AS ivt_seqnum,
        NVL((
            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 ROWNUM = 1
              AND LENGTH(iil.SEGMENT1) >= 1
        ), 'AAAAAAA') 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
    WHERE ESI.INVENTORY_ITEM_ID = 100000110904412
),
Unioned_Queries AS (
    SELECT * FROM First_Query
    UNION ALL
    SELECT * FROM Second_Query
),
All_Queries AS (
    SELECT item_number, subinventory_code, INV_LOCATOR, QUERY, ivt_seqnum, priority,
        ROW_NUMBER() OVER (PARTITION BY priority, item_number, subinventory_code ORDER BY priority DESC, priority) AS Overall_Priority
    FROM Unioned_Queries
)
SELECT * FROM All_Queries
WHERE NOT (
    priority = 2
    AND INV_LOCATOR = 'AAAAAAA'
    AND EXISTS (
        SELECT 1 FROM First_Query FQ
        WHERE FQ.item_number = All_Queries.item_number
          AND FQ.subinventory_code = All_Queries.subinventory_code
    )
)

d r · Answer 2 · 2024-11-17T16:29:24+08:00

由于联合数据中已经有了第 1 个和第 2 个查询标志 - 您可以在联合查询中使用 Row_Number() Over() 分析函数，但稍后可以在 Row_Number() 的 Order By 部分和带有 Case 表达式的外部查询的 Where 子句中使用第 1/2 个标志。不过，您可以保留 IVT_SEQNUM 列，但将有另一个 Row_Number() 列用作外部查询的 Where 子句中的过滤器...

您可以使用 union sql 代码声明一个 CTE ...

WITH
  your_union_sql_data AS
     -- put your union sql below - resulting as the first table from the question
    ( Select   12923 as ITEM_NUMBER, '41OPR' as SUBINVENTORY_CODE, 'Z-B-01-02-C' as INV_LOCATOR, '1st Query' as QUERY, 1 as IVT_SEQNUM,  1 as PRIORITY, 1 as OVERALL_PRIORITY From Dual Union All 
      Select   12923, '41OPR', 'W-130-00-00-00', '1st Query', 2, 1, 2 From Dual Union All
      Select   12923, '11OPR', 'AAAAAAA',        '2nd Query', 1, 2, 1 From Dual Union All 
      Select   12923, '41OPR', 'AAAAAAA',        '2nd Query', 1, 2, 1 From Dual
    )

...并使用第一个/第二个查询标志对行进行排序以进行编号...

--      M a i n    S Q L : 
SELECT   ITEM_NUMBER, SUBINVENTORY_CODE, INV_LOCATOR, QUERY, IVT_SEQNUM, PRIORITY, OVERALL_PRIORITY
FROM   ( Select    a.*, 
                   Row_Number() Over(Partition By ITEM_NUMBER, SUBINVENTORY_CODE 
                                Order By ITEM_NUMBER, QUERY, SUBINVENTORY_CODE) as RN_ITEM_SUBINV 
         From      your_union_sql_data  a
       )
WHERE  RN_ITEM_SUBINV = Case When INV_LOCATOR = 'AAAAAAA' And QUERY = '2nd Query'
                             Then 1
                        Else RN_ITEM_SUBINV
                        End
ORDER BY  ITEM_NUMBER, QUERY, SUBINVENTORY_CODE

结果：

ITEM_NUMBER	子库存代码	入口定位器	询问	顺序编号	优先事项	总优先级
12923	41OPR	ZB-01-02-C	第一个查询	1	1	1
12923	41OPR	W-130-00-00-00	第一个查询	2	1	2
12923	11OPR	啊啊啊啊啊啊	第二个查询	1	2	1

小提琴

对从 UNION 查询中获取的数据进行优先排序/过滤

`(表达式，左值) = 右值` 在 C 或 C++ 中是有效的赋值吗？为什么有些编译器会接受/拒绝它？

何时应使用 std::inplace_vector 而不是 std::vector？

在 C++ 中，一个不执行任何操作的空程序需要 204KB 的堆，但在 C 中则不需要

如果 T 既不可构造、不可复制、也不可移动，那么我可以拥有 std::optional<T> 吗？

为什么我可以定义一个 constinit 的 std::string 实例？如果对象需要动态初始化，constinit 不是被禁止的吗？

如何分配以后放置的新“如同新”

PowerBI 目前与 BigQuery 不兼容：Simba 驱动程序与 Windows 更新有关

将 NULL 和 nullptr 传递给模板参数有什么区别？

AdMob：MobileAds.initialize() - 对于某些设备，“java.lang.Integer 无法转换为 java.lang.String”

我正在尝试仅使用海龟随机和数学模块来制作吃豆人游戏

对从 UNION 查询中获取的数据进行优先排序/过滤

2 个回答

相关问题