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

select
    a.job,
    a.mon first_year_mon,
    a.cnt first_year_cnt,
    b.mon second_year_mon,
    b.cnt second_year_cnt
from
    (
        select
            job,
            date_format(date, '%Y-%m') mon,
            sum(num) cnt
        from
            resume_info
        where
            year(date) = '2025'
        group by
            mon,
            job
    ) a
    join (
        select
            job,
            date_format(date, '%Y-%m') mon,
            sum(num) cnt
        from
            resume_info
        where
            year(date) = '2026'
        group by
            mon,
            job
    ) b on a.job = b.job
    and right(a.mon,2) = right(b.mon,2)
order by
    first_year_mon desc,
    a.job desc