题解 | 更新员工信息表

--先求更新表的每位employee_id的最后一次更新数据
with tiaojian as (
select 
EMPLOYEE_ID,
UPDATE_DT,
NEW_POSITION
from(
select 
EMPLOYEE_ID,
UPDATE_DT,
NEW_POSITION,
dense_rank()over(partition by EMPLOYEE_ID order by UPDATE_DT desc) as py
from EMPLOYEE_UPDATE
) as t 
where
t.py=1
)
--连接表并判断更新日期和初始日期的大小，谁大输出谁的。
select 
t.employee_id,
case when UPDATE_DT>ef.LAST_UPDATE_DT then NEW_POSITION else position end as POSITION,
case when UPDATE_DT>ef.LAST_UPDATE_DT then UPDATE_DT else LAST_UPDATE_DT end  as LAST_UPDATE_DT
from tiaojian t inner join  EMPLOYEE_INFO ef 
on t.EMPLOYEE_ID=ef.EMPLOYEE_ID
order by t.employee_id