题解 | 更新员工信息表

SELECT
    t1.EMPLOYEE_ID,
    IF (
        UPDATE_DT > LAST_UPDATE_DT,
        NEW_POSITION,
        POSITION
    ) AS POSITION,
    IF (
        UPDATE_DT > LAST_UPDATE_DT,
        UPDATE_DT,
        LAST_UPDATE_DT
    ) AS LAST_UPDATE_DT
from
    (
        select
            *
        from
            (
                select
                    EMPLOYEE_ID,
                    UPDATE_DT,
                    NEW_POSITION,
                    row_number() over (
                        partition by
                            EMPLOYEE_ID
                        order by
                            UPDATE_DT desc
                    ) as rk
                from
                    EMPLOYEE_UPDATE
            ) tt
        where
            rk = 1
    ) t1
    join EMPLOYEE_INFO t2 using (EMPLOYEE_ID)
ORDER BY
    EMPLOYEE_ID