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

SELECT fir.job, fir.first_year_mon, fir.first_year_cnt, sec.second_year_mon,
    sec.second_year_cnt
FROM(
  
    SELECT  job, date_format(date,'%Y-%m') AS first_year_mon,
    SUM(num) AS first_year_cnt
    FROM resume_info
    WHERE YEAR(date) = 2025
    GROUP BY first_year_mon,job
) fir
JOIN(

    SELECT  job, date_format(date,'%Y-%m') AS second_year_mon,
    SUM(num) AS second_year_cnt
    FROM resume_info
    WHERE YEAR(date) = 2026
    GROUP BY second_year_mon,job 
) sec ON fir.job = sec.job AND RIGHT(fir.first_year_mon,2) = RIGHT(sec.second_year_mon,2)
ORDER BY fir.first_year_mon DESC, fir.job DESC