题解 | 更新员工信息表
更新员工信息表
https://www.nowcoder.com/practice/1eb20d4bf7c5443da7b84105372c9070
WITH rank_update AS (
SELECT *,
RANK() OVER(PARTITION BY EMPLOYEE_ID ORDER BY UPDATE_DT DESC) AS rank_dt
FROM EMPLOYEE_UPDATE
),
last_update AS (
SELECT * FROM rank_update
WHERE rank_dt = 1
)
SELECT a.EMPLOYEE_ID,
CASE WHEN a.LAST_UPDATE_DT < b.UPDATE_DT THEN b.NEW_POSITION
ELSE a.POSITION END AS POSITION,
CASE WHEN a.LAST_UPDATE_DT < b.UPDATE_DT THEN b.UPDATE_DT
ELSE a.LAST_UPDATE_DT END AS LAST_UPDATE_DT
FROM EMPLOYEE_INFO AS a
LEFT JOIN last_update AS b
ON a.EMPLOYEE_ID = b.EMPLOYEE_ID
ORDER BY EMPLOYEE_ID;