题解 | 更新员工信息表

#先找出更新表中的日期排序
WITH UPDATE_DT_RANKED AS 
(SELECT
    EMPLOYEE_ID,
    UPDATE_DT,
    NEW_POSITION,
    ROW_NUMBER() OVER (PARTITION BY EMPLOYEE_ID ORDER BY UPDATE_DT DESC) AS rk

FROM
    EMPLOYEE_UPDATE)
#连接两张表，筛选出符合条件的数据
SELECT
    e.EMPLOYEE_ID,
    CASE WHEN u.UPDATE_DT>e.LAST_UPDATE_DT THEN u.NEW_POSITION ELSE e.POSITION END AS POSITION,
    CASE WHEN u.UPDATE_DT>e.LAST_UPDATE_DT THEN u.UPDATE_DT ELSE e.LAST_UPDATE_DT END AS LAST_UPDATE_DT
FROM
    EMPLOYEE_INFO AS e
    INNER JOIN
    UPDATE_DT_RANKED AS u ON e.EMPLOYEE_ID=u.EMPLOYEE_ID
WHERE
    u.rk=1
ORDER BY
    e.EMPLOYEE_ID;