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

select
    b.job,b.first_year_mon,first_year_cnt,
    c.second_year_mon,c.second_year_cnt
from
    (
        select
            a.job,
            substr(a.date, 1, 7) as first_year_mon,
            sum(num) as first_year_cnt
        from
            resume_info a
            where a.date like '2025%'
        group by
            a.job,
            substr(a.date, 1, 7)
    ) b
    left join (
        select
            a.job,
            substr(a.date, 1, 7) as second_year_mon,
            sum(num) as second_year_cnt
        from
            resume_info a
             where a.date like '2026%'
        group by
            a.job,
            substr(a.date, 1, 7)
    )c
    on b.job =c.job 
    and substr(b.first_year_mon,1,4)+1=substr(c.second_year_mon,1,4)
    and substr(b.first_year_mon,6,2)=substr(c.second_year_mon,6,2)
    order by first_year_mon desc, 
    job desc