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

select *
from
(select t1.job,t1.first_year_mon,t1.first_year_cnt,
t2.second_year_mon,t2.second_year_cnt from
(SELECT
job,DATE_FORMAT(date,'%Y-%m') as first_year_mon,sum(num) as first_year_cnt
from resume_info
where year(date) = '2025'
group by job,first_year_mon) t1,
(SELECT
job,DATE_FORMAT(date,'%Y-%m') as second_year_mon,sum(num) as second_year_cnt
from resume_info
where year(date) = '2026'
group by job,second_year_mon) t2
where t1.job = t2.job
and substring_index(t1.first_year_mon,'-',-1) = substring_index(t2.second_year_mon,'-',-1)) t
group by job,first_year_mon
order by first_year_mon desc,job desc