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

本题就是2个结果进行组合
1个是2025年的结果 然后 join 2026年的结果

select job,date_format(date,'%Y-%m') as first_year_mon,sum(num) as first_year_cnt,date_format(date,'%m') as f1 from resume_info
where year(date)=2025
group by first_year_mon ,job

这里就能把2025年的结果挑出来

select job,date_format(date,'%Y-%m') as decond_yaer_mon,sum(num) as second_year_cnt,date_format(date,'%m') as f2 from resume_info
where year(date)=2026
group by decond_yaer_mon ,job

把2026年的结果调出来
当时合并的时候用 job相等合并，当时带来的问题就是 不同的月也合并在了一起，所以把月单独抽出来，作为一个限制条件

select a.job,a.first_year_mon,a.first_year_cnt,b.decond_yaer_mon,b.second_year_cnt from 
(select job,date_format(date,'%Y-%m') as first_year_mon,sum(num) as first_year_cnt,date_format(date,'%m') as f1 from resume_info
where year(date)=2025
group by first_year_mon ,job ) as a 
left join 
(select job,date_format(date,'%Y-%m') as decond_yaer_mon,sum(num) as second_year_cnt,date_format(date,'%m') as f2 from resume_info
where year(date)=2026
group by decond_yaer_mon ,job ) as b
on a.job=b.job and a.f1=b.f2
order by a.first_year_mon desc,a.job desc