题解 | 更新员工信息表

select ei.EMPLOYEE_ID,
       case when LAST_UPDATE_DT > UPDATE_DT then POSITION else NEW_POSITION end    as POSITION,
       case when LAST_UPDATE_DT > UPDATE_DT then LAST_UPDATE_DT else UPDATE_DT end as LAST_UPDATE_DT
from EMPLOYEE_INFO ei
         join (select UPDATE_DT, e.EMPLOYEE_ID, NEW_POSITION
               from EMPLOYEE_UPDATE e
                        join (select max(UPDATE_DT) as m, EMPLOYEE_ID
                              from EMPLOYEE_UPDATE
                              group by EMPLOYEE_ID) eu on e.EMPLOYEE_ID = eu.EMPLOYEE_ID and e.UPDATE_DT = eu.m) b
              on b.EMPLOYEE_ID = ei.EMPLOYEE_ID
order by ei.EMPLOYEE_ID;