题解 | 更新员工信息表

with EMPLOYEE_INFO2 as (select
EMPLOYEE_ID
,UPDATE_DT 
,NEW_POSITION
from (
    select
    EMPLOYEE_ID
    ,UPDATE_DT 
    ,NEW_POSITION
    ,rank()over(partition by EMPLOYEE_ID order by UPDATE_DT desc) rk
    from EMPLOYEE_UPDATE
) t 
where rk = 1)
select 
EMPLOYEE_INFO.EMPLOYEE_ID
,if (UPDATE_DT > LAST_UPDATE_DT, NEW_POSITION, POSITION) as POSITION
,if (UPDATE_DT > LAST_UPDATE_DT, UPDATE_DT, LAST_UPDATE_DT) as LAST_UPDATE_DT
from EMPLOYEE_INFO
join EMPLOYEE_INFO2
on EMPLOYEE_INFO.EMPLOYEE_ID = EMPLOYEE_INFO2.EMPLOYEE_ID