用相同月份和岗位做2025及2026表链接

with t1 as(
    select 
        date_format(date,'%Y-%m') as first_year_mon,
        sum(num) as first_year_cnt,
        job
    from resume_info
    where YEAR(date) = '2025'
    group by date_format(date,'%Y-%m'),job
),
t2 as(
    select 
        date_format(date,'%Y-%m') as second_year_mon,
        sum(num) as second_year_cnt,
        job
    from resume_info
    where Year(date) = '2026'
    group by date_format(date,'%Y-%m'),job
)
select 
    t1.job,first_year_mon,first_year_cnt, second_year_mon,second_year_cnt
from 	# 用 job 和 不同年的相同月份连接
    t1 join t2 on t1.job = t2.job 
    and right(t1.first_year_mon,2)=right(t2.second_year_mon,2)
order by 
    first_year_mon DESC,job DESC