题解 | 更新员工信息表

with zzh as (
    select e2.EMPLOYEE_ID ,UPDATE_DT ,NEW_POSITION ,
    row_number()over(partition by e2.EMPLOYEE_ID order by UPDATE_DT desc) rk
    from EMPLOYEE_UPDATE e2
)
select zzh.EMPLOYEE_ID,
if(UPDATE_DT > LAST_UPDATE_DT,NEW_POSITION,POSITION) as POSITION ,
if(UPDATE_DT > LAST_UPDATE_DT,UPDATE_DT ,LAST_UPDATE_DT ) as LAST_UPDATE_DT
from zzh join EMPLOYEE_INFO e1 on zzh.EMPLOYEE_ID = e1.EMPLOYEE_ID
where zzh.rk = 1 
order by zzh.EMPLOYEE_ID