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

# 1.先把2025年和2026年的数据分别求出来
# 2.再左连接2025年的数据，两个临时表的连接条件有两个：一是job一致，二是月份一致；
with t1 as (
select job,left(date,7) as mon,sum(num) as cnt
from resume_info
where left(date,4) = 2025
group by 1,2
),
t2 as (
select job,left(date,7) as mon,sum(num) as cnt
from resume_info
where left(date,4) = 2026
group by 1,2
)
select t1.job,t1.mon as first_year_mon,t1.cnt as first_year_cnt
    ,t2.mon as second_year_mon,t2.cnt as second_year_cnt
from t1 
left join t2 
on t1.job = t2.job and right(t1.mon,2) = right(t2.mon,2)
order by first_year_mon desc,job desc;