题解 | 更新员工信息表

SELECT
    ei.EMPLOYEE_ID,
    -- 如果 eu.EMPLOYEE_ID 是 NULL (表示该员工无更新记录)，则直接用主表职位。
    -- 否则，比较日期，决定使用新职位还是旧职位。
    CASE
        WHEN eu.EMPLOYEE_ID IS NULL THEN ei.POSITION
        WHEN eu.update_dt > ei.LAST_UPDATE_DT THEN eu.NEW_POSITION
        ELSE ei.POSITION
    END AS POSITION,
    -- 日期的逻辑与上面类似
    CASE
        WHEN eu.EMPLOYEE_ID IS NULL THEN ei.LAST_UPDATE_DT
        WHEN eu.update_dt > ei.LAST_UPDATE_DT THEN eu.update_dt
        ELSE ei.LAST_UPDATE_DT
    END AS LAST_UPDATE_DT
FROM
    EMPLOYEE_INFO AS ei
    LEFT JOIN (
        -- 从 EMPLOYEE_UPDATE 表中，为每个员工找出“唯一且最晚”的那条记录。
        SELECT
            t1.EMPLOYEE_ID,
            t1.NEW_POSITION,
            t1.update_dt
        FROM
            EMPLOYEE_UPDATE AS t1
            INNER JOIN (
                -- 1. 先用 GROUP BY 找出每个员工最晚的更新时间
                SELECT
                    EMPLOYEE_ID,
                    MAX(update_dt) AS max_dt
                FROM
                    EMPLOYEE_UPDATE
                GROUP BY
                    EMPLOYEE_ID
            ) AS t2
            -- 2. 然后用这个最晚时间去和原表做JOIN，把对应的那整行记录(包括职位)给捞出来
            ON t1.EMPLOYEE_ID = t2.EMPLOYEE_ID
            AND t1.update_dt = t2.max_dt
    ) AS eu ON ei.EMPLOYEE_ID = eu.EMPLOYEE_ID
ORDER BY
    ei.EMPLOYEE_ID;

# with
#     temp as (
#         row_number() over (
#             partition by
#                 employee_id
#             order by
#                 update_dt desc
#         ) as rn,
#         employee_id,
#         new_position,
#         update_dt
#         from
#             EMPLOYEE_UPDATE
#     )
# select
#     EMPLOYEE_ID,
#     case
#         when last_update_dt < update_dt then new_position
#         else position
#     end as position
# from
#     EMPLOYEE_INFO ei
#     left join temp t on t.EMPLOYEE_ID = ei.EMPLOYEE_ID
#     and t.rn = 1;