题解 | #实习广场投递简历分析(三)#
实习广场投递简历分析(三)
https://www.nowcoder.com/practice/83f84aa5c32b4cf5a75558d02dd7743c
with sum_m as ( select job,date_format(date,'%Y-%m') mon,sum(num) as cnt from resume_info group by job,mon ) select s1.job,s1.mon first_year_mon,s1.cnt first_year_cnt,s2.mon second_year_mon,s2.cnt second_year_cnt from sum_m s1 inner join sum_m s2 on s1.job=s2.job and substring_index(s1.mon,'-',-1)=substring_index(s2.mon,'-',-1) //月份相同 and substring_index(s1.mon,'-',1)=substring_index(s2.mon,'-',1)-1 //年份多1年 where substring_index(s1.mon,'-',1)='2025' order by s1.mon desc,s1.job desc
with sum_m as (
select job,date_format(date,'%Y-%m') mon,sum(num) as cnt
from resume_info
group by job,mon
)
先对原表按月份汇总得到每个job每月的投递数量,这里注意用date_format函数之后返回的日期(mon)变成了字符串格式,新表数据存在sum_m里面
做一次内连接,因为现在mon是字符串,所以可以用string_index来比较年份和月份,分隔符号为‘-’,筛选出第二年同月的投递总数
连接条件是,s1.job=s2.job、月份相同substring_index(s1.mon,'-',-1)=substring_index(s2.mon,'-',-1)、年份相差1年 substring_index(s1.mon,'-',1)=substring_index(s2.mon,'-',1)-1
然后筛选2025、排序输出