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

SELECT t1.job, fym, first_year_cnt, sym, second_year_cnt
FROM 
(SELECT job, SUBSTR(date, 1, 7) AS fym, SUM(num) AS first_year_cnt
FROM resume_info
WHERE YEAR(date) = 2025
GROUP BY job, fym) t1
JOIN
(SELECT job, SUBSTR(date, 1, 7) AS sym, SUM(num) AS second_year_cnt
FROM resume_info
WHERE YEAR(date) = 2026
GROUP BY job, sym) t2
ON t1.job = t2.job
AND SUBSTR(t1.fym, 1, 4) = SUBSTR(t2.sym, 1, 4) - 1
AND SUBSTR(t1.fym, 6, 2) = SUBSTR(t2.sym, 6, 2)
ORDER BY fym DESC, job DESC