题解 | 更新员工信息表

WITH t AS (
    SELECT EMPLOYEE_ID,UPDATE_DT,NEW_POSITION
    FROM EMPLOYEE_UPDATE
    WHERE (EMPLOYEE_ID,UPDATE_DT) IN (
        SELECT EMPLOYEE_ID,MAX(UPDATE_DT)
        FROM EMPLOYEE_UPDATE
        GROUP BY EMPLOYEE_ID
    )
)
SELECT t.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 t
LEFT JOIN EMPLOYEE_INFO  e ON t.EMPLOYEE_ID=e.EMPLOYEE_ID
ORDER BY t.EMPLOYEE_ID