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

select
	t1.job
	,date_format(t1.date,'%Y-%m') as first_year_mon
	,sum(t1.cnt) as first_year_cnt
	,date_format(t2.date,'%Y-%m') as second_year_mon
	,sum(t2.cnt) as second_year_cnt
from 
	(select 
		job
		,date
		,sum(num) as cnt 
	from resume_info 
	group by job,date_format(date,'%Y-%m')) t1 #first_year各月汇总
join
	(select 
		job
		,date
		,sum(num) as cnt 
	from resume_info 
	group by job,date_format(date,'%Y-%m')) t2 #second_year各月汇总
on t1.job = t2.job 
and date_format(t1.date,'%Y-%m') = date_format(date_sub(t2.date,interval 1 year),'%Y-%m')
where year(t1.date) = '2025'
group by t1.job,date_format(t1.date,'%Y-%m')
order by first_year_mon desc,t1.job desc

记录一下解题过程中踩过的坑；

1. 时间函数参数date要为标准形式； 连接条件最开始写的： date_format(t1.date,'%Y-%m') = date_sub(date_format(t2.date,'%Y-%m'),interval 1 year) 试过之后发现不会报错但会返回空值
2. 汇总求和时顺序错误； 最初我是按照年份差1、月份相同做连接之后再求和。但这样同一个月份之间的多条数据会形成笛卡尔积，导致计数重复。