题解 | 更新员工信息表

select
e.EMPLOYEE_ID,
if(e.LAST_UPDATE_DT<=t1.LAST_UPDATE_DT,t1.POSITION,e.POSITION) as POSITION,
if(e.LAST_UPDATE_DT<=t1.LAST_UPDATE_DT,t1.LAST_UPDATE_DT,e.LAST_UPDATE_DT) as LAST_UPDATE_DT
from EMPLOYEE_INFO e
left join
(select
t.EMPLOYEE_ID,
t.POSITION,
t.LAST_UPDATE_DT
from
(select
    a.EMPLOYEE_ID,
    a.NEW_POSITION as POSITION,
    if(a.UPDATE_DT=MAX(b.UPDATE_DT),a.UPDATE_DT,null) as LAST_UPDATE_DT
from
    EMPLOYEE_UPDATE a
join
    EMPLOYEE_UPDATE b
on a.EMPLOYEE_ID=b.EMPLOYEE_ID
group by a.EMPLOYEE_ID,a.UPDATE_DT,a.NEW_POSITION) t
where t.LAST_UPDATE_DT is not null) t1
on e.EMPLOYEE_ID=t1.EMPLOYEE_ID