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

select 
    a.job    as job 
    ,a.mon1  as first_year_mon	
    ,a.cnt1  as first_year_cnt
    ,b.mon2  as second_year_mon
    ,b.cnt2  as second_year_cnt
from
(
select 
    job
    ,substring(date,1,7) mon1
    ,sum(num) cnt1 
from 
    resume_info 
where year(date)=2025 
group by 
    job,mon1
order by 
    mon1 desc ,job desc
) a
left join 
(
select 
    job
    ,substring(date,1,7) mon2
    ,sum(num) cnt2 
from 
    resume_info 
where year(date)=2026
group by 
    job,mon2
order by 
    mon2 desc ,job desc
) b 
on a.job=b.job 
and substring(a.mon1,5,6)=substring(b.mon2,5,6)