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

select y1.job, y1.year1 , cnt1 , y2.year2 , cnt2
from 
(
select job ,DATE_FORMAT(date, '%Y-%m') as year1 , DATE_FORMAT(date, '%m' ) as mon1 , sum(num) as cnt1 
from resume_info
where  year(date) = '2025'
group by job , year1 , mon1  ) as y1 
join 
(
select job ,DATE_FORMAT(date, '%Y-%m') as year2 ,DATE_FORMAT(date, '%m') as mon2 , sum(num) as cnt2 
from resume_info
where  year(date) = '2026'
group by job , year2, mon2  ) as y2 
on y1.mon1=y2.mon2  and  y1.job=y2.job 
order by y1.year1 desc, job desc