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

select h1.job,
h1.first_year_mon,h1.cnt as first_year_cnt,
h2.second_year_mon,h2.cnt as second_year_cnt
from
(select job,substr(date,1,7) as first_year_mon,
sum(num) as cnt
from resume_info
where year(date) =2025
group by job,substr(date,1,7))h1
join 
(select job,substr(date,1,7) as second_year_mon,
sum(num) as cnt
from resume_info
where year(date) =2026
group by job,substr(date,1,7))h2
on h1.job = h2.job 
and right(h1.first_year_mon,2)=right(h2.second_year_mon,2)
order by h1.first_year_mon desc,h1.job desc

没啥思路 难点在于一行同时取第一个月和第二个月的数，可以通过建立2张表，2张表通过job和月份相等进行连接