题解 | 更新员工信息表

select EMPLOYEE_ID,POSITION,LAST_UPDATE_DT
from (select EMPLOYEE_ID,if (max(UPDATE_DT) > LAST_UPDATE_DT,NEW_POSITION,POSITION) as POSITION,
case 
when max(UPDATE_DT) > LAST_UPDATE_DT then max(UPDATE_DT)
else LAST_UPDATE_DT
end as LAST_UPDATE_DT,row_number()over(partition by EMPLOYEE_ID order by LAST_UPDATE_DT,UPDATE_DT desc) t
from EMPLOYEE_INFO E1 join EMPLOYEE_UPDATE E2 using(EMPLOYEE_ID)
group by EMPLOYEE_ID,E2.NEW_POSITION,E2.UPDATE_DT) as temp
where t = 1
#这个表我也不知道这么写出来的，分组错了几次，莫名写对了，有大佬帮忙看看吗
select EMPLOYEE_ID,if (max(UPDATE_DT) > LAST_UPDATE_DT,NEW_POSITION,POSITION) as POSITION,
case 
when max(UPDATE_DT) > LAST_UPDATE_DT then max(UPDATE_DT)
else LAST_UPDATE_DT
end as LAST_UPDATE_DT,row_number()over(partition by EMPLOYEE_ID order by LAST_UPDATE_DT,UPDATE_DT desc) t
from EMPLOYEE_INFO E1 join EMPLOYEE_UPDATE E2 using(EMPLOYEE_ID)
group by EMPLOYEE_ID,E2.NEW_POSITION,E2.UPDATE_DT