题解 | 更新员工信息表

with temp as (
    select 
    eu.employee_id,
    eu.UPDATE_DT,
    new_position,
    row_number() over(partition by EMPLOYEE_ID order by UPDATE_DT desc) as rnk
    from EMPLOYEE_UPDATE eu
),

temp1 as (
    select 
    ei.employee_id as EMPLOYEE_ID,
    case when 
    tp.update_dt > ei.last_update_dt then tp.new_position else ei.position end as POSITION,
    case when 
    tp.update_dt > ei.last_update_dt then tp.update_dt else ei.last_update_dt end as LAST_UPDATE_DT
    from EMPLOYEE_INFO ei 
    left join temp tp
    on ei.employee_id = tp.employee_id
    and tp.rnk = 1

)
select * from temp1
order by employee_id asc