题解 | 更新员工信息表

select EMPLOYEE_ID,
case when LAST_UPDATE_DT_new>LAST_UPDATE_DT then NEW_POSITION else POSITION end as POSITION,
case when LAST_UPDATE_DT_new>LAST_UPDATE_DT then LAST_UPDATE_DT_new else LAST_UPDATE_DT end as LAST_UPDATE_DT
from (
    select a.EMPLOYEE_ID, POSITION, LAST_UPDATE_DT, NEW_POSITION, UPDATE_DT,
    max(UPDATE_DT) over (partition by a.EMPLOYEE_ID) as LAST_UPDATE_DT_new
    from EMPLOYEE_INFO a join EMPLOYEE_UPDATE b on a.EMPLOYEE_ID=b.EMPLOYEE_ID
) temp
where UPDATE_DT=LAST_UPDATE_DT_new
order by EMPLOYEE_ID