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

select
    b.job,
    first_year_mon,
    first_year_cnt,
    second_year_mon,
    second_year_cnt
from
    (
        select
            job,
            mon as first_year_mon,
            sum(num) as first_year_cnt
        from
            (
                select 
                    *,
                    substr(date, 1, 7) as mon
                from
                    resume_info
                where date<'2026-01-01'
            ) a
        group by
            job, mon
        order by
            first_year_mon desc,job desc
    ) b 
left join
(
    select
        job,
        mon as second_year_mon,
        sum(num) as second_year_cnt
    from
        (
            select 
                *,
                substr(date, 1, 7) as mon
            from
                resume_info
        ) aa
    where mon>'2025-12' and mon<'2027-01'
    group by
        job, mon
    order by 
        second_year_mon desc,job desc
) c
on 
    b.job = c.job and substr(b.first_year_mon,6,2) = substr(c.second_year_mon,6,2)