题解 | 更新员工信息表

# 首先连接两个表，找出每个id的最晚更新时间，进一步筛选即可
select distinct EMPLOYEE_ID, POSITION, LAST_UPDATE_DT
from(
    select EMPLOYEE_ID, POSITION, LAST_UPDATE_DT,
    rank()over(partition by EMPLOYEE_ID order by LAST_UPDATE_DT desc) as posn
from(
    select EMPLOYEE_ID, POSITION, LAST_UPDATE_DT
    from EMPLOYEE_INFO
    union all
    select EMPLOYEE_ID, NEW_POSITION as POSITION, UPDATE_DT as LAST_UPDATE_DT
    from EMPLOYEE_UPDATE
) as a
) as b
where b.posn=1
order by EMPLOYEE_ID