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

with a as (
    select job, substring(date, 1, 7) as mon, sum(num) as cnt
    from resume_info
    where substring(date, 1, 4) in ('2025', '2026')
    group by job, mon
)
select a1.job as job, a1.mon as first_year_mon, a1.cnt as first_year_cnt, a2.mon as second_year_mon, a2.cnt as second_year_cnt
from a a1 join a a2 on a1.job = a2.job and substring(a1.mon, 6, 2) = substring(a2.mon, 6, 2)
where substring(a1.mon, 1, 4) = '2025' and substring(a2.mon, 1, 4) = '2026'
order by first_year_mon desc, a1.job desc

不困难，CTE自身连接即可。