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

with a as
(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, date_format(date,'%Y-%m')),
 b as(
    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, date_format(date,'%Y-%m')
 )

 select a.job,first_year_mon,first_year_cnt,second_year_mon,second_year_cnt
 from a join b on a.job = b.job
 where date_format(date_sub(str_to_date(concat(second_year_mon,'-01'),'%Y-%m-%d'),interval 1 year),'%Y-%m') = first_year_mon
 order by first_year_mon desc,a.job desc

二刷 难点在于日期转为字符串