LEFT JOIN分组查询异常，无法获取正确最新事件记录求助

看起来你遇到的核心问题是用MAX()聚合导致字段错位——MAX()会分别提取每个字段的最大值，但这些最大值可能来自不同的行，所以事件码和日期无法对应；另外，窗口函数的分区键设置不对，导致没能正确锁定每个(ship_id, ship_to_loc_code)组的最新记录。下面是具体的解决思路和重构后的代码：

问题根源拆解 #

1. MAX() + GROUP BY的误用：你用MAX()分别取ship_evnt_cd和ship_evnt_tms，但这两个值可能来自TABLE.D中不同的行（比如最大的事件码来自A行，最新的日期来自B行），自然会出现数据不匹配。
2. 窗口函数分区键不完整：TABLE.D的子查询里只按ship_id分区，但你的关联逻辑是ship_id + ship_to_loc_code，同一个ship_id可能对应多个不同的ship_to_loc_code，这会导致窗口函数错误地跨loc取最新记录。
3. TABLE.C的排序无效：直接在子查询里ORDER BY updt_job_tms DESC但没有窗口函数或LIMIT，数据库会忽略这个排序（因为子查询返回的是无序集合），所以关联时还是随机匹配行。

解决方案：先预处理最新记录，再关联 #

我们需要先给每个需要获取最新记录的表（TABLE.C和TABLE.D）用窗口函数标记出每个分组的最新行，再和主表关联，彻底避免聚合函数导致的字段错位。

重构后的代码 #

WITH 
-- 预处理TABLE.C中CUS类型的最新记录（按ship_id+ship_to_loc_code分组）
latest_scus AS (
    SELECT 
        ship_id,
        ship_to_loc_code,
        shipment_tms,
        updt_job_tms
    FROM (
        SELECT 
            *,
            ROW_NUMBER() OVER (
                PARTITION BY ship_id, ship_to_loc_code 
                ORDER BY updt_job_tms DESC
            ) AS rn
        FROM TABLE.C
        WHERE loc_type = 'CUS'
    ) t
    WHERE rn = 1
),
-- 预处理TABLE.C中CDC类型的最新记录
latest_scdc AS (
    SELECT 
        ship_id,
        ship_to_loc_code,
        shipment_tms,
        updt_job_tms
    FROM (
        SELECT 
            *,
            ROW_NUMBER() OVER (
                PARTITION BY ship_id, ship_to_loc_code 
                ORDER BY updt_job_tms DESC
            ) AS rn
        FROM TABLE.C
        WHERE loc_type = 'CDC'
    ) t
    WHERE rn = 1
),
-- 预处理TABLE.D中排除9开头事件的最新记录（按ship_id_856+ship_to_loc_cd856分组）
latest_ship_events AS (
    SELECT 
        ship_id_856,
        ship_to_loc_cd856,
        ship_evnt_cd,
        ship_evnt_tms,
        carr_tracking_num,
        event_srv_lvl
    FROM (
        SELECT 
            *,
            ROW_NUMBER() OVER (
                PARTITION BY ship_id_856, ship_to_loc_cd856 
                ORDER BY updt_job_tms DESC
            ) AS rn
        FROM TABLE.D
        WHERE LEFT(ship_evnt_cd, 1) <> '9'
    ) t
    WHERE rn = 1
)
SELECT 
    z.po_id,
    scdc.ship_id AS ship_id_cdc,
    evnt_cdc.ship_evnt_cd AS last_event_cdc,
    evnt_cdc.ship_evnt_tms AS event_tms_cdc,
    scus.ship_id AS ship_id_cus,
    evnt_cus.ship_evnt_cd AS last_event_cus,
    evnt_cus.ship_evnt_tms AS event_tms_cus
FROM TABLE.A z
LEFT JOIN (
    SELECT DISTINCT 
        po_id, 
        iltc.ship_id, 
        s.ship_to_loc_code 
    FROM TABLE.B iltc 
    INNER JOIN TABLE.C s 
        ON iltc.ship_id = s.ship_id 
        AND iltc.ship_to_loc_code = s.ship_to_loc_code 
        AND s.ship_to_ctry <> ' '
) AS A ON z.po_id = a.po_id
-- 关联CUS类型的最新TABLE.C记录
LEFT JOIN latest_scus scus 
    ON A.SHIP_ID = scus.SHIP_ID 
    AND A.SHIP_TO_LOC_CODE = scus.SHIP_TO_LOC_CODE 
    AND DAYS(scus.shipment_tms) + 10 >= DAYS(z.ship_tms)
-- 关联CDC类型的最新TABLE.C记录
LEFT JOIN latest_scdc scdc 
    ON A.SHIP_ID = scdc.SHIP_ID 
    AND A.SHIP_TO_LOC_CODE = scdc.SHIP_TO_LOC_CODE 
    AND DAYS(scdc.shipment_tms) + 10 >= DAYS(z.ship_tms)
-- 关联CUS对应的最新TABLE.D事件
LEFT JOIN latest_ship_events evnt_cus 
    ON scus.ship_id = evnt_cus.ship_id_856 
    AND scus.ship_to_loc_code = evnt_cus.ship_to_loc_cd856
-- 关联CDC对应的最新TABLE.D事件
LEFT JOIN latest_ship_events evnt_cdc 
    ON scdc.ship_id = evnt_cdc.ship_id_856 
    AND scdc.ship_to_loc_code = evnt_cdc.ship_to_loc_cd856
WHERE z.po_id = 'T1DLDC'

关键优化点说明 #

1. CTE预处理最新记录：用WITH子句提前处理每个表的最新行，确保每个(ship_id, ship_to_loc_code)组只返回最新的一条记录，避免关联时产生笛卡尔积。
2. 完整的窗口分区键：所有窗口函数都用ship_id + ship_to_loc_code作为分区键，匹配你的关联逻辑，确保不会跨location取错误的最新记录。
3. 去掉GROUP BY和MAX()：通过预处理子查询，主查询不需要再聚合，每个字段都来自同一条最新记录，彻底解决字段错位问题。
4. TABLE.C的筛选逻辑前置：在CTE里就过滤loc_type，减少后续关联的数据量，提升查询效率。

如果还是没获取到X1事件，建议检查：

• TABLE.D中对应(ship_id_856, ship_to_loc_cd856)的记录里，updt_job_tms最新的那条是否确实是X1事件（可能最新的更新时间对应的不是X1，需要确认排序字段是否正确，比如是否应该用ship_evnt_tms而不是updt_job_tms）。
• 关联条件是否完全匹配，比如ship_to_loc_code的大小写、空格是否一致（可以用TRIM()处理后再关联）。

内容的提问来源于stack exchange，提问作者Juan Ignacio Durante