题解 | 更新员工信息表

select
        ei.EMPLOYEE_ID,
        if(ei.LAST_UPDATE_DT < per_eu.UPDATE_DT,per_eu.NEW_POSITION,ei.POSITION) as POSITION,
        if(ei.LAST_UPDATE_DT < per_eu.UPDATE_DT,per_eu.UPDATE_DT,ei.LAST_UPDATE_DT) as LAST_UPDATE_DT 
from 
        EMPLOYEE_INFO as ei
left join (
    select
        a.EMPLOYEE_ID,
        a.UPDATE_DT,
        eu.NEW_POSITION  
    from (
        select
            EMPLOYEE_ID,
            max(UPDATE_DT) as UPDATE_DT
        from 
                EMPLOYEE_UPDATE
        group by 
                EMPLOYEE_ID 
    )a
    left join 
            EMPLOYEE_UPDATE as eu
    on 
            a.EMPLOYEE_ID = eu.EMPLOYEE_ID
    and
            a.UPDATE_DT = eu.UPDATE_DT
) as per_eu
on 
        ei.EMPLOYEE_ID = per_eu.EMPLOYEE_ID
order by 
        ei.EMPLOYEE_ID