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

select t5.job as job, first_year_mon, first_year_cnt, seconddate as second_year_mon, second_year_cnt
from
(select job, first_year_mon, sum(num) as first_year_cnt
from
(select job, date_format(date, '%Y-%m') as first_year_mon, num
from resume_info
where date >= '2025-01-01' and date <= '2025-12-31') as t4
group by job, first_year_mon) as t5
join
(select job, secondyear, seconddate, sum(num) as second_year_cnt
from 
(select job, date_format(secondyear, '%Y-%m') as secondyear, date_format(seconddate, '%Y-%m') as seconddate, num 
from
(select job, date as seconddate, date_sub(date, interval 1 year) as secondyear, num
from resume_info
where date >= '2026-01-01' and date <= '2026-12-31') as t1) as t2
group by job, secondyear, seconddate) as t3
on t5.job = t3.job and t5.first_year_mon = t3.secondyear
order by first_year_mon desc, job desc