题解 | 更新员工信息表

with t1 as (
    select EMPLOYEE_ID,max(UPDATE_DT) as UPDATE_DT
    from EMPLOYEE_UPDATE eu
    group by EMPLOYEE_ID
),
t2 as (
    select EMPLOYEE_ID,NEW_POSITION,UPDATE_DT
    from EMPLOYEE_UPDATE eu
    where (EMPLOYEE_ID,UPDATE_DT) in (select EMPLOYEE_ID,UPDATE_DT from t1)
)
select 
    ei.EMPLOYEE_ID,
    case
      when UPDATE_DT>LAST_UPDATE_DT then t2.NEW_POSITION
      else ei.POSITION end as POSITION,
    case
      when UPDATE_DT>LAST_UPDATE_DT then t2.UPDATE_DT
      else ei.LAST_UPDATE_DT end as LAST_UPDATE_DT
from EMPLOYEE_INFO ei
join t2 on ei.EMPLOYEE_ID=t2.EMPLOYEE_ID
order by ei.EMPLOYEE_ID