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

-- 获得每月岗位的投递总数
with each_month_job as (
    select job,SUBSTRING(y_month,1,4) as year,SUBSTRING(y_month,6,2) as month,y_month, sum_num from(
    select job,SUBSTRING(date,1,7) as y_month,sum(num) as sum_num from resume_info group by job,y_month
    )a 
)
-- 自己链接自己
select a.job,a.y_month,a.sum_num,b.y_month,b.sum_num 
from each_month_job a inner join each_month_job b on a.month = b.month and a.job = b.job
where a.year = '2025' and b.year = '2026'
order by a.y_month desc ,a.job desc 

效率不高，不过简单明白