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

主要的思路就是分别查找并将两个部分进行联结，注意这里联结对应的月份一致，即：

month(a.date) = month(b.date)

month()函数的参数只能是完整的[年-月-日]，即利用date_format转化后的日期不能再用month函数。

select 
a.job,
date_format(a.date, '%Y-%m') as first_year_mon, 
a.first_year_cnt, 
date_format(b.date, '%Y-%m') as second_year_mon,
second_year_cnt 
from 
(select job, date, sum(num) as first_year_cnt
 from resume_info
 where year(date) = '2025'
 group by month(date), job) a 
inner join

(select job, date, sum(num) as second_year_cnt
 from resume_info
 where year(date) = '2026'
 group by month(date), job) b 

on a.job = b.job and month(a.date) = month(b.date)
order by first_year_mon desc, a.job desc;