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

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,'20%y-%m') as first_year_mon, sum(num) as first_year_cnt from resume_info
where year(date) = '2025'
group by job, month(date)
order by first_year_mon desc,job desc) as t1
left join
(select job,  date_format(date,'20%y-%m') as second_year_mon, sum(num) as second_year_cnt from resume_info
where year(date) = '2026'
group by job, month(date)
order by second_year_mon desc,job desc)as t2
on t1.job = t2.job and month(t1.first_year_mon) = month(t2.second_year_mon)
order by t1.first_year_mon desc, t1.job desc

报错：2026年的投简历情况没有显示

问题好像出现在left join的on条件上，要求月份相同的限制出了毛病。初步怀疑可能是因为date_format()函数得到的数值类型不是日期，因此不能使用month()函数提取出月份。暂时尝试使用字符串提取的方式。substring(t1.first_year_mon,6,2) = substring(t2.second_year_mon,6,2)

修改后

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,'20%y-%m') as first_year_mon, sum(num) as first_year_cnt from resume_info
where year(date) = '2025'
group by job, month(date)
order by first_year_mon desc,job desc) as t1
left join
(select job,  date_format(date,'20%y-%m') as second_year_mon, sum(num) as second_year_cnt from resume_info
where year(date) = '2026'
group by job, month(date)
order by second_year_mon desc,job desc)as t2
on t1.job = t2.job and substring(t1.first_year_mon,6,2) = substring(t2.second_year_mon,6,2)  #修改这个部分
order by t1.first_year_mon desc, t1.job desc

成功啦！