题解 | 更新员工信息表

WITH
T1 AS(
    SELECT
        EMPLOYEE_ID,
        UPDATE_DT,
        NEW_POSITION,
        RANK()OVER(PARTITION BY EMPLOYEE_ID ORDER BY UPDATE_DT DESC) AS DRANK
    FROM
        EMPLOYEE_UPDATE
)
,
T2 AS(
    SELECT
        EMPLOYEE_ID,
        UPDATE_DT,
        NEW_POSITION
    FROM
        T1
    WHERE
        DRANK=1
)
,
T3 AS(
    SELECT
        EMPLOYEE_ID,
        (
            CASE
                WHEN UPDATE_DT>LAST_UPDATE_DT THEN NEW_POSITION
                ELSE POSITION
            END
        ) AS POSITION,
        (
            CASE
                WHEN UPDATE_DT>LAST_UPDATE_DT THEN UPDATE_DT
                ELSE LAST_UPDATE_DT
            END
        ) AS LAST_UPDATE_DT
    FROM
       EMPLOYEE_INFO LEFT JOIN T2 USING(EMPLOYEE_ID) 
)

SELECT * FROM T3