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

# 加max聚合函数的原因是因为再对2025和2026俩个表进行连接的时候，需要根据job和月份进行匹配，而我尝试用 
# month(a.mon)=month(b.mon)时，无法连接上数据，不到何原因，所以只能在a表和b表中添加一个max聚合函数来确定每
# 个月的最大日期，最后在连接时，可以用month()来匹配2025和2026对应的月份

select
a.job
,a.mon as first_year_mon
,a.cnt as first_year_cnt
,b.mon as second_year_mon
,b.cnt as second_year_cnt
from(
select
a.job
,date_format(a.date,'%Y-%m') as mon
,max(a.date) as "date"
,sum(a.num) as cnt
from resume_info a
where year(a.date)=2025
group by a.job,date_format(a.date,'%Y-%m')
) a
left join(
select
a.job
,date_format(a.date,'%Y-%m') as mon
,max(a.date) as "date"
,sum(a.num) as cnt
from resume_info a
where year(a.date)=2026
group by a.job,date_format(a.date,'%Y-%m')
) b
on a.job=b.job and month(a.date)=month(b.date)
order by first_year_mon desc,a.job desc
;