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

with
tb2025 as(
    select job, date_format(date, "%Y-%m") as first_year_mon, sum(num) as first_year_cnt 
    from resume_info
    where year(date) = '2025'
    group by job, first_year_mon
),
tb2026 as(
    select job, date_format(date, "%Y-%m") as second_year_mon, sum(num) as second_year_cnt 
    from resume_info
    where year(date) = '2026'
    group by job, second_year_mon
)
select tb2025.job, first_year_mon, first_year_cnt, second_year_mon, second_year_cnt from tb2025
join tb2026 on tb2025.job = tb2026.job and right(first_year_mon, 2) = right(second_year_mon, 2)
order by first_year_mon desc, job desc