题解 | 更新员工信息表

WITH 
NEW_UPDATE AS (
    SELECT 
       EMPLOYEE_ID,
       ROW_NUMBER()OVER(PARTITION BY EMPLOYEE_ID ORDER BY UPDATE_DT DESC) rk,
       NEW_POSITION,
       UPDATE_DT
    FROM
       EMPLOYEE_UPDATE
)
SELECT 
   i.EMPLOYEE_ID,
   CASE 
       WHEN i.LAST_UPDATE_DT>=u.UPDATE_DT THEN i.POSITION
       ELSE u.NEW_POSITION
   END AS POSITION,
   CASE 
       WHEN i.LAST_UPDATE_DT>=u.UPDATE_DT THEN i.LAST_UPDATE_DT
       ELSE u.UPDATE_DT
   END AS LAST_UPDATE_DT
FROM 
   EMPLOYEE_INFO i
JOIN
   NEW_UPDATE u
ON 
   i.EMPLOYEE_ID = u.EMPLOYEE_ID
WHERE
    rk = 1
ORDER BY
    i.EMPLOYEE_ID ASC;