题解 | 更新员工信息表

select 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(
	select EMPLOYEE_ID,UPDATE_DT,NEW_POSITION
	from(
		select EMPLOYEE_ID,UPDATE_DT,NEW_POSITION,row_number()over(partition by  EMPLOYEE_ID order 			by UPDATE_DT desc) as rk
		from  EMPLOYEE_UPDATE)a
	where rk=1)b
join EMPLOYEE_INFO c using(EMPLOYEE_ID)
order by EMPLOYEE_ID