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

延续前面问题思路，分别找出2025年、2026年每月各个岗位的简历数,按照岗位，月份分组

#（1）2025年按岗位分组

```select job, DATE_FORMAT(date,'%Y-%m') as first_year_mon
,sum(num) as first_year_cnt
      from resume_info
where year(date) = 2025
group by 1,2
     order by 2
#（2）2026年按岗位分组
(select job, DATE_FORMAT(date,'%Y-%m') as second_year_mon
,sum(num) as second_year_cnt from resume_info
where year(date) = 2026
group by 1,2
order by 2
#(3) 将(1)(2)中的两个表连接，连接条件是工作岗位，但如果仅仅连接岗位名称，会出现“笛卡尔积”现象，造成冗余
所以，需要将2025年与2026年的月份对应，提取出对应月份相等
select b.job,a.first_year_mon,a.first_year_cnt,b.second_year_mon,b.second_year_cnt
from (select job, DATE_FORMAT(date,'%Y-%m') as first_year_mon
,sum(num) as first_year_cnt
      from resume_info
where year(date) = 2025
group by 1,2
     order by 2)a
left join 
(select job, DATE_FORMAT(date,'%Y-%m') as second_year_mon
,sum(num) as second_year_cnt from resume_info
where year(date) = 2026
group by 1,2
order by 2)b
on a.job = b.job
# AND left(a.first_year_mon,4)=left(b.second_year_mon,4)-1 -- 年份差1
AND substr(a.first_year_mon,6,2)=substr(b.second_year_mon,6,2) -- 月份相同
# and year(a.first_year_mon) = year(b.second_year_mon)+1
# and month(a.first_year_mon) = month (b.second_year_mon)
group by  1,2,4
order by 2 desc,1 desc