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

with
    t as (
        select
            id,
            job,
            DATE_FORMAT (date, '%Y-%m') as date,
            num
        from
            resume_info
    ),
    y25 as (
        select
            job,
            date as first_year_mon,
            CAST(RIGHT(date, 2) AS UNSIGNED) as month,
            sum(num) as first_year_cnt
        from
            t
        WHERE
            date LIKE '2025-%'
        group by
            job,
            first_year_mon
        order by
            first_year_mon desc,
            job desc
    ),
    y26 as (
        select
            job,
            date as second_year_mon,
            CAST(RIGHT(date, 2) AS UNSIGNED) as month,
            sum(num) as second_year_cnt
        from
            t
        WHERE
            date LIKE '2026-%'
        group by
            job,
            second_year_mon
    )
select
    y25.job,
    y25.first_year_mon,
    y25.first_year_cnt,
    y26.second_year_mon,
    y26.second_year_cnt
from
    y25
    left join y26 on y25.job = y26.job
    and y25.month = y26.month