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

select a1.job,a1.date1 as first_year_mon,a1.f_num as first_year_cnt,
a2.date2 as second_year_mon,a2.s_num as second_year_cnt
from
(select substr(date,1,7) as date1,job,sum(num) as f_num
from resume_info
where year(date)='2025'
group by substr(date,1,7),job
) as a1
inner join
(
select substr(date,1,7) as date2,job,sum(num) as s_num
from resume_info
where year(date)='2026'
group by substr(date,1,7),job
) as a2 on a1.job=a2.job and right(a1.date1,2)=right(a2.date2,2)
order by  a1.date1 desc,a1.job desc