题解 | 更新员工信息表

select EMPLOYEE_ID ,
    case when UPDATE_DT>LAST_UPDATE_DT  then NEW_POSITION  else POSITION  end as POSITION ,
    case when UPDATE_DT>LAST_UPDATE_DT  then UPDATE_DT else LAST_UPDATE_DT end as LAST_UPDATE_DT
from (
    select *,
    rank() over(partition by EMPLOYEE_ID  order by UPDATE_DT desc) as rk
    from EMPLOYEE_UPDATE
)temp
 right join EMPLOYEE_INFO using(EMPLOYEE_ID )
 where rk=1
 order by EMPLOYEE_ID