题解 | 更新员工信息表

select
    EMPLOYEE_ID,
    POSITION,
    LAST_UPDATE_DT
from
    (
        select
            EMPLOYEE_ID,
            POSITION,
            LAST_UPDATE_DT,
            ROW_NUMBER() over (
                partition by
                    EMPLOYEE_ID
                order by
                    LAST_UPDATE_DT desc
            ) as ranks
        from
            (
                select
                    EMPLOYEE_INFO.EMPLOYEE_ID,
                    POSITION,
                    LAST_UPDATE_DT
                from
                    EMPLOYEE_INFO
                union
                select
                    EMPLOYEE_UPDATE.EMPLOYEE_ID,
                    NEW_POSITION as POSITION,
                    UPDATE_DT as LAST_UPDATE_DT
                from
                    EMPLOYEE_UPDATE
            ) as t1
    ) as t2
where
    ranks = 1;