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

这个题用的窗口函数，感觉还蛮简单
把问题分开来看，先写2025年的，再写2026年的
SELECT job,left(date,7)as first_year_num, sum(num) as first_year_cnt
,row_number() OVER(order by date,job ) as paixu
from resume_info
where left(date,4)='2025'
GROUP BY first_year_num,job
order by first_year_num desc,job desc
使用窗口函数，是为了两个表连接的时候有相同的一列

然后2026年也一样的方法
SELECT job,left(date,7)as second_year_num, sum(num) as second_year_cnt
,row_number() OVER(order by date,job ) as paixu
from resume_info
where left(date,4)=2026
GROUP BY second_year_num,job
order by second_year_num desc,job desc

然后两个表把paixu连接一下就可以啦
代码汇总如下
SELECT a.job,a.first_year_num,a.first_year_cnt,b.second_year_num,b.second_year_cnt from
(SELECT job,left(date,7)as first_year_num, sum(num) as first_year_cnt
,row_number() OVER(order by date,job ) as paixu
from resume_info
where left(date,4)='2025'
GROUP BY first_year_num,job
order by first_year_num desc,job desc
) a
inner join
(SELECT job,left(date,7)as second_year_num, sum(num) as second_year_cnt
,row_number() OVER(order by date,job ) as paixu
from resume_info
where left(date,4)=2026
GROUP BY second_year_num,job
order by second_year_num desc,job desc) b
on a.paixu=b.paixu