题解 | #实习广场投递简历分析(三)#

with auxi_table as
(
select job,`year_month`,sum(num) as num,rk from
(
select job,
date_format(date,"%Y-%m") `year_month`,
num,
dense_rank() over (partition by job order by year(date)) as rk
from resume_info
)
A
group by job, `year_month`,rk
)
select t1.job,t1.`year_month` first_year_mon,t1.num,t2.`year_month`,t2.num
from auxi_table as t1 left join auxi_table as t2
on t1.job=t2.job and t1.rk=1 and t2.rk=2
where right(t1.`year_month`,2)=right(t2.`year_month`,2)
order by first_year_mon desc, t1.job desc