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

select t1.job,t1.first_year_mon,t1.first_year_cnt,t2.second_year_mon,t2.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 job,first_year_mon) t1
inner 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 job,second_year_mon) t2
on 
   t1.job=t2.job and right(first_year_mon,2)=right(second_year_mon,2)
                        )
order by first_year_mon desc,job desc

总体思路：2025年与2026年进行内连接

• 连接条件是个坑，job和date作为连结条件均不唯一（因为无法保证是同一个job然后不同年的同一月，单单连结job和date都无法做到这个条件）因此不能作为连结条件，会报错

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 job,first_year_mon

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 job,second_year_mon

2.内连接 时

注意：#job相同和DATE_FORMAT(date,'%Y-%m') 后两位日期相同

#添加新字段concat(job,month(date)作为连接条件