题解 | 更新员工信息表

with t1 as(
  select
 EMPLOYEE_ID,
 NEW_POSITION ,
 UPDATE_DT,
 row_number()over(partition by EMPLOYEE_ID  order by UPDATE_DT desc) rn
from
EMPLOYEE_UPDATE  
),
t2 as(
   select
 EMPLOYEE_ID,
 NEW_POSITION,
 UPDATE_DT
from
t1
where rn=1 
)

select
a.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
EMPLOYEE_INFO a left join t2 b on a.EMPLOYEE_ID=b.EMPLOYEE_ID