题解 | 更新员工信息表

with eu as (select EMPLOYEE_ID ,
    UPDATE_DT,
    row_number() over(partition by EMPLOYEE_ID order by UPDATE_DT desc) as rk,
    NEW_POSITION 
from EMPLOYEE_UPDATE)
select EMPLOYEE_ID,
    case when UPDATE_DT > LAST_UPDATE_DT then NEW_POSITION else POSITION end as POSITION,
    case when UPDATE_DT > LAST_UPDATE_DT then UPDATE_DT else LAST_UPDATE_DT end as LAST_UPDATE_DT
from EMPLOYEE_INFO ei
inner join eu
    using(EMPLOYEE_ID)
where rk = 1
order by EMPLOYEE_ID