题解 | 更新员工信息表

with tmp as(
select
    EMPLOYEE_ID,
    UPDATE_DT,
    NEW_POSITION
from(
select 
    EMPLOYEE_ID,
    rank() over(partition by EMPLOYEE_ID order by UPDATE_DT desc) rk,
    UPDATE_DT,
    NEW_POSITION
from EMPLOYEE_UPDATE
)a
where rk =1
)
select
    t.EMPLOYEE_ID,
    if(UPDATE_DT>LAST_UPDATE_DT,NEW_POSITION,POSITION) POSITION,
    if(UPDATE_DT>LAST_UPDATE_DT,UPDATE_DT,LAST_UPDATE_DT) LAST_UPDATE_DT
from tmp t left join EMPLOYEE_INFO e on t.EMPLOYEE_ID=e.EMPLOYEE_ID
order by t.EMPLOYEE_ID