题解 | 更新员工信息表

with updatetime as
(
    select
        employee_id,
        new_position,
        update_dt,
        rank() over(partition by employee_id order by update_dt desc) as timerk
        from  EMPLOYEE_UPDATE
),
latestime as 
(
    select
        employee_id,
        new_position,
        update_dt
    from updatetime
    where timerk=1
)

select
    e.employee_id as EMPLOYEE_ID,
    case when e.last_update_dt >= l.update_dt then e.position 
    else l.new_position end as POSITION,
    case when e.last_update_dt >= l.update_dt then e.last_update_dt
    else l.update_dt end as LAST_UPDATE_DT
from EMPLOYEE_INFO e
join latestime l on e.employee_id = l.employee_id
order by employee_id;

再次爱上分块逻辑，就是这个表名我还蛮疑惑的，我自己cmd写的时候不用在乎大小写的。