需要检索表的最后插入/更新的记录，并包含一些排除项 [英] Need to retrieve table's last inserted/updated record with some exclusions

问题描述

我正在尝试检索在表SHIPMENT_EVENT中插入/更新的最后一条记录。其中有一些不需要的事件/代码，例如所有以 9开头的事件/代码。问题是这些事件始终是最后发送的事件，但对我来说最不重要，因此我需要显示最后一个事件，但是如果此事件是9X事件，则不应显示它，而应继续查找对于最后一个事件，直到到达有效代码才能显示之前，它不是9X。

I am trying to retrieve the last record inserted/updated in the table SHIPMENT_EVENT. There are a couple of these events/codes that are not needed for instance all beginning with '9'. The issue is that these events are always the last to be sent but are the least important to me so I need to display the last event, but if this one is a 9X event, then it should not display it, instead it should continue looking for the last event which is not 9X until reaching a valid code to show.

以下是我认为对我的情况有所帮助的代码。此左联接应检查事件表并检索上一个事件。但是，通过深层测试，当最后一个事件是9X时，它显示的是NULL。

Here is the code I have that thought was helping my case. This left join should check the event table and retrieve the last event. However, by testing deeply saw that it was showing NULL instead, when the last event was a 9X.

我尝试将NOT LIKE子句移到第一个之外块，但结果相同。我注意到直接删除not like时是错误的，最终结果是9X事件。

I've tried moving the NOT LIKE clause arround, outside the first block, but with the same result. I noticed it was wrong when removing directly the not like, I ended up with all results wit the 9X event.

left join 

(    select ship_id, ship_to_loc_cd856, ship_evnt_cd, updt_job_tms, 
carr_tracking_num, event_srv_lvl
         FROM
         (   select  ship_id, ship_to_loc_cd856, ship_evnt_cd, 
updt_job_tms, carr_tracking_num, event_srv_lvl
            ,        row_number() over(partition by ship_id order by 
updt_job_tms desc) as RN
          FROM     shipment_event
         ) s
         WHERE RN = 1  and ship_evnt_cd not like ('9%')
        )  AS lscus
ON        scus.ship_id=lscus.ship_id and 
scus.ship_to_loc_code=lscus.ship_to_loc_cd856

我需要检查最近事件并进行检索的逻辑，但是如果它是9X，则继续寻找下一个非9x事件。

I would need the logic to check the most recent event and retrieve, but if it is 9X then continue looking for the next non 9x event.

谢谢

推荐答案

您的问题是您正在过滤掉不需要的事件之后获得最近的行：

Your problem is that you're filtering out undesired events after getting the most recent row:

WHERE RN = 1  and ship_evnt_cd not like ('9%')

您需要先进行过滤，然后从左：

You need to filter first, and get the most-recent row from those that are left:

LEFT JOIN (SELECT ship_id, ship_to_loc_cd856, ship_evnt_cd, 
                  updt_job_tms, carr_tracking_num, event_srv_lvl, 
                  ROW_NUMBER() OVER(PARTITION BY ship_id ORDER BY updt_job_tms DESC) AS rn
           FROM Shipment_Event
           -- This _might_ perform better than the NOT LIKE condition, you'll need to test
           WHERE LEFT(ship_evnt_cd, 1) <> '9') lscus
       ON lscus.ship_id = scus.ship_id 
          AND lscus.ship_to_loc_cd856 = scus.ship_to_loc_code 
          AND lscus.rn = 1

这篇关于需要检索表的最后插入/更新的记录，并包含一些排除项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！