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

WITH resume_2025 AS(
    SELECT job, DATE_FORMAT(r.date, '%Y-%m') AS first_year_mon, 
    SUM(r.num) AS first_year_cnt
    FROM resume_info AS r
    WHERE YEAR(r.date) = 2025
    GROUP BY job, first_year_mon
), 
resume_2026 AS(
    SELECT job, DATE_FORMAT(r.date, '%Y-%m') AS second_year_mon, 
    SUM(r.num) AS second_year_cnt
    FROM resume_info AS r
    WHERE YEAR(r.date) = 2026
    GROUP BY job, second_year_mon
)
SELECT r_2025.job, first_year_mon, first_year_cnt, second_year_mon, second_year_cnt 
FROM resume_2025 AS r_2025
INNER JOIN resume_2026 AS r_2026
ON RIGHT(r_2025.first_year_mon, 2) = RIGHT(r_2026.second_year_mon, 2)
AND r_2025.job = r_2026.job
ORDER BY first_year_mon DESC, r_2025.job DESC;