题解 | 更新员工信息表

with
    t1 as (
        select
            EMPLOYEE_ID,
            NEW_POSITION,
            UPDATE_DT,
            row_number() over(partition by EMPLOYEE_ID order by UPDATE_DT desc) as rk
            /*根据时间修改的还有职位信息，所以如果直接用max取最晚更新时间，对应的position不会改变，对应的信息错误，有关联项要用排序函数取最值*/
        from
            EMPLOYEE_UPDATE
    )

select t1.EMPLOYEE_ID
/*注意逻辑关系*/
,case when t1.UPDATE_DT > t2.LAST_UPDATE_DT then t1.NEW_POSITION else t2.POSITION end as POSITION
,case when t1.UPDATE_DT > t2.LAST_UPDATE_DT then t1.UPDATE_DT else t2.LAST_UPDATE_DT end as LAST_UPDATE_DT
from EMPLOYEE_INFO t2
left join t1 on t2.EMPLOYEE_ID = t1.EMPLOYEE_ID
where rk = 1 /*取最值*/
order by EMPLOYEE_ID