题解 | 更新员工信息表

with u1 as(
    select EMPLOYEE_ID,UPDATE_DT,NEW_POSITION,row_number()over(partition by EMPLOYEE_ID order by UPDATE_DT desc) as rk
    from EMPLOYEE_UPDATE),
    u2 as(
    select EMPLOYEE_ID,UPDATE_DT,NEW_POSITION
    from u1 where rk=1)
select e.EMPLOYEE_ID as EMPLOYEE_ID,case when u2.UPDATE_DT>=e.LAST_UPDATE_DT then u2.NEW_POSITION else e.POSITION end as POSITION,
case when u2.UPDATE_DT>=e.LAST_UPDATE_DT then u2.UPDATE_DT else e.LAST_UPDATE_DT end as LAST_UPDATE_DT
from u2 join EMPLOYEE_INFO e on u2.EMPLOYEE_ID=e.EMPLOYEE_ID
order by EMPLOYEE_ID