题解 | 更新员工信息表

WITH tp as(select EMPLOYEE_ID,NEW_POSITION, UPDATE_DT,row_number() over(partition by EMPLOYEE_ID ORDER BY UPDATE_DT desc) AS rk
from EMPLOYEE_UPDATE
),

tp2 as (select EMPLOYEE_ID,NEW_POSITION, UPDATE_DT
from tp 
where rk =1
)

select a.EMPLOYEE_ID, if(LAST_UPDATE_DT < UPDATE_DT,t.NEW_POSITION,a.POSITION) as POSITION, if(LAST_UPDATE_DT < UPDATE_DT,UPDATE_DT,LAST_UPDATE_DT) as LAST_UPDATE_DT
from EMPLOYEE_INFO as a join tp2 as t on a.EMPLOYEE_ID = t.EMPLOYEE_ID