题解 | 更新员工信息表

with emp1 as
(select * from EMPLOYEE_INFO
union all
select EMPLOYEE_ID, NEW_POSITION as POSITION,UPDATE_DT as LAST_UPDATE_DT
from EMPLOYEE_UPDATE),
emp2 as
(select  
    EMPLOYEE_ID,
    POSITION,
    LAST_UPDATE_DT,
    row_number() over(partition by EMPLOYEE_ID order by LAST_UPDATE_DT desc) as rk
from emp1
group by 1,2,3)

select EMPLOYEE_ID,POSITION,LAST_UPDATE_DT
from emp2
where rk=1
order by EMPLOYEE_ID;