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

select
    t1.job,
    t1.first_year_mon,
    t1.first_year_cnt,
    t2.second_year_mon,
    t2.second_year_cnt
from (
    -- 2025年每月总数
    select
        job,
        substr(date,1,7) first_year_mon,
        sum(num) first_year_cnt
    from resume_info
    where substr(date,1,4) = '2025'
    group by job,first_year_mon
) t1
inner join (
    -- 2026年每月总数
    select
        job,
        substr(date,1,7) second_year_mon,
        sum(num) second_year_cnt
    from resume_info
    where substr(date,1,4) = '2026'
    group by job,second_year_mon
) t2
on t1.job = t2.job and regexp_replace(t1.first_year_mon,'2025','2026') = t2.second_year_mon 
order by first_year_mon desc,job desc ;