题解 | 更新员工信息表
更新员工信息表
https://www.nowcoder.com/practice/1eb20d4bf7c5443da7b84105372c9070
WITH t1 AS( SELECT employee_id, max(update_dt) UPDATE_DT FROM EMPLOYEE_UPDATE GROUP BY employee_id ) SELECT t1.EMPLOYEE_ID, IF(t1.UPDATE_DT > ei.LAST_UPDATE_DT, eu.NEW_POSITION, ei.POSITION) POSITION, IF(t1.UPDATE_DT > ei.LAST_UPDATE_DT, t1.UPDATE_DT, ei.LAST_UPDATE_DT) LAST_UPDATE_DT FROM t1 JOIN EMPLOYEE_INFO ei ON ei.employee_id = t1.employee_id JOIN EMPLOYEE_UPDATE eu ON t1.employee_id = eu.employee_id AND t1.UPDATE_DT = eu.UPDATE_DT ORDER BY t1.EMPLOYEE_ID