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

拉两个新表，分别存2025年和2026年每月每种语言的cnt。然后将两个表连接起来，这里要注意的是有两个连接键，job和月份。所以要把月从日期里提取出来

with temp1 as 
(select job, date_format(date, "%Y-%m") as mon, sum(num) as cnt
from resume_info
where year(date) = "2025"
group by job, mon
order by mon desc, cnt desc),
temp2 as 
(select job, date_format(date, "%Y-%m") as mon, sum(num) as cnt
from resume_info
where year(date) = "2026"
group by job, mon
order by mon desc, cnt desc)

select temp1.job, temp1.mon as first_year_mon, 
       temp1.cnt as first_year_cnt,
       temp2.mon as second_year_mon,
       temp2.cnt as second_year_cnt
from temp1 left join temp2
on temp1.job = temp2.job and right(temp1.mon, 2) = right(temp2.mon, 2)
order by first_year_mon desc, temp1.job desc