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

select
    t2025.job,
    t2025.first_year_mon,
    t2025.first_year_cnt,
    t2026.second_year_mon,
    t2026.second_year_cnt
from 
    (select
        t1.job job,
        DATE_FORMAT(t1.date,'%Y-%m') first_year_mon,
        sum(t1.num) first_year_cnt,
        DATE_FORMAT(t1.date,'%m') month
    from
        resume_info t1
    where
        year(t1.date)  = 2025
    group by
        t1.job,DATE_FORMAT(t1.date,'%Y-%m'),DATE_FORMAT(t1.date,'%m')) t2025
left join
    (select
        t2.job job,
        DATE_FORMAT(t2.date,'%Y-%m') second_year_mon,
        sum(t2.num) second_year_cnt,
        DATE_FORMAT(t2.date,'%m') month
    from
        resume_info t2
    where
        year(t2.date)  = 2026
    group by
        t2.job,DATE_FORMAT(t2.date,'%Y-%m'),DATE_FORMAT(t2.date,'%m')) t2026
on
    t2025.job = t2026.job
    and t2025.month= t2026.month
order by
    t2025.first_year_mon desc,
    t2025.job desc