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

with t1 as(
select job,date_format(date,'%Y-%m') first_year_mon,sum(num) first_year_cnt
from resume_info
where year(date)=2025
group by job,date_format(date,'%Y-%m')
order by first_year_mon desc,job desc 
),
t2 as(
select job,date_format(date,'%Y-%m') second_year_mon,sum(num) second_year_cnt
from resume_info
where year(date)=2026
group by job,date_format(date,'%Y-%m')
order by second_year_mon desc,job desc
)

select t1.job,first_year_mon,first_year_cnt,second_year_mon,second_year_cnt
from t1
left join t2
on t1.job=t2.job and right(t1.first_year_mon,2)=right(t2.second_year_mon,2)