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

select t1.job,t1.first_year_mon,t1.first_year_cnt,
t2.second_year_mon,t2.second_year_cnt
from
(select job,
-- concat(job,month(date))as id,
date_format(date,'%Y-%m')as first_year_mon,
sum(num)as first_year_cnt -- y是两位数25，Y才是四位数2025
from resume_info
where year(date)=2025
group by job,date_format(date,'%Y-%m')
)as t1
inner join
(select job,
-- concat(job,month(date))as id,
date_format(date,'%Y-%m')as second_year_mon,
sum(num)as second_year_cnt -- y是两位数25，Y才是四位数2025
from resume_info
where year(date)=2026
group by job,date_format(date,'%Y-%m')
)as t2 on t1.job=t2.job
where right(first_year_mon,2)=right(second_year_mon,2)
order by t1.first_year_mon desc,t1.job desc
;
-- 直接利用job进行连接无法保证月份也保持一致，所以要对月份再次进行筛选