题解 | #牛客的课程订单分析(七)#

WITH s AS(SELECT 
ri.job,
LEFT(ri.date,7)  as mon,
sum(ri.num)  as cnt
FROM resume_info ri
GROUP BY ri.job,LEFT(ri.date,7)
ORDER BY LEFT(ri.date,7) desc, ri.job desc)

SELECT 
s.job,
s.mon AS first_year_mon,
s.cnt AS first_year_cnt,
s1.mon  AS scond_year_mon,
s1.cnt  AS scond_year_cnt 
FROM s
LEFT JOIN s s1 ON s1.job =s.job 
AND left(s.mon,4)+1 = left(s1.mon,4)
AND RIGHT(s.mon,2) = RIGHT(s1.mon,2)
WHERE LEFT(s.mon,4) ='2025'

直脑子的想法 但是比较直观