主页 / user-4408559

Nick's questions

Martin Hope
Nick
Asked: 2024-11-17 03:57:16 +0800 CST
  • 5

我有以下查询中的以下数据样本，其中我有两个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'
sql
Martin Hope
Nick
Asked: 2024-09-27 20:06:32 +0800 CST
  • 5

我正在寻找一个 Excel 函数，它可以检查一列并查找第一个字符为 0 的单元格。

在以下示例中，您可以看到下方列中总共有 3 个单元格以 0 作为第一个字符。我只想获取该列的这些单元格的数量。我考虑过使用公式，COUNTIF但不确定如何确保它只检查第一个字符，因为其他地方可能存在 0（例如 10/10、5/100 等）。

在此处输入图片描述

excel
Martin Hope
Nick
Asked: 2024-09-25 04:09:08 +0800 CST
  • 5

我在 Oracle DB 中有以下 SQL，我期望它返回 2024 年 3 月 1 日 (01-MAR-24)，但它却返回了 01-AUG-24

SELECT TRUNC(TRUNC (SYSDATE, 'MM') - 6, 'MM')
FROM dual

有人能告诉我为什么会出现这种情况吗？我想选择几个月前的第一天，格式如上所述，但这并不符合我的预期。

sql