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

with tab as
(select job,substring_index(date,'-',2) as date,sum(num) as num
from resume_info
where date like '2025%'
group by substring_index(date,'-',2),job
order by date desc,job desc
),tab2 as
(
    select job,substring_index(date,'-',2) as date,sum(num) as num
from resume_info
where date like '2026%'
group by substring_index(date,'-',2),job
order by date desc,job desc
)
select tab.job,tab.date as first_year_mon	,tab.num as first_year_cnt	, tab2.date as second_year_mon
,tab2.num as second_year_cnt
from tab inner join tab2 on tab.job = tab2.job and left(tab.date,4) = left(tab2.date,4)-1 and right(tab.date,2) = right(tab2.date,2)