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

按照上一题的思路，子查询分别得到25年和26年的两个表，通过where job=job,月份=月份，将两张表连接起来即可，感觉将job和月份组合新建一个主键也可以解决这个问题。

select A.job,
A.mon as first_year_mon,
A.cnt as first_year_cnt,
B.mon as second_year_mon,
B.cnt as second_year_cnt
from 
(select job,left(date,7) mon,sum(num) as cnt
from resume_info
where year(date)="2025"
group by job,mon)A,
(select job,left(date,7) mon,sum(num) as cnt
from resume_info
where year(date)="2026"
group by job,mon)B
where A.job=B.job and right(A.mon,2)=right(B.mon,2)
order by A.mon desc,A.job desc