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

with t as (
    select job, date_format(date, '%Y-%m') as month, sum(num) as cnt
    from resume_info
    where year(date) in (2025, 2026)
    group by job, date_format(date, '%Y-%m')
), t_2025 as (
    select *
    from t
    where left(month, 4)=2025
), t_2026 as (
    select *
    from t
    where left(month, 4)=2026
)
select t1.job, t1.month as first_year_mon, t1.cnt as first_year_cnt,
t2.month as second_year_mon, t2.cnt as second_year_cnt
from t_2025 t1 join t_2026 t2 on t1.job=t2.job and right(t1.month, 2)=right(t2.month, 2)
order by first_year_mon desc, t1.job desc