幽默了，想到哪写到哪，题解 | 实习广场投递简历分析(三)

select 2025job as job, concat(substring(monnum1, 1, 4), '-', substring(monnum1, 5, 6)) as first_year_mon, cnt_1 as first_year_cnt, concat(substring(monnum2, 1, 4), '-', substring(monnum2, 5, 6)) as second_year_cnt, cnt_2 as second_year_cnt
from
(select t3.job as 2025job, t3.monnum1, t3.cnt_1, t4.job as 2026job, t4.monnum2, t4.cnt_2 from 
(select job, if(mon1<10, concat(20250, mon1), concat(2025, mon1)) as monnum1, cnt_1 from(
select job, month(date) as mon1, sum(num) as cnt_1 from resume_info where Year(date)=2025
group by month(date), job) as t1) as t3
inner join 
(select job, if(mon2<10, concat(20260, mon2), concat(2026, mon2)) as monnum2, cnt_2 from(
select job, month(date) as mon2, sum(num) as cnt_2 from resume_info where Year(date)=2026
group by month(date), job) as t2) as t4
on t4.monnum2 = t3.monnum1+100 and t3.job = t4.job) as t5
order by first_year_mon desc, job desc