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

SELECT
    d1.job,
    d1.mon AS first_year_mon,
    d1.cnt AS first_year_cnt,
    d2.mon AS second_year_mon,
    d2.cnt AS second_year_cnt
FROM
    (SELECT
         job,
         DATE_FORMAT(date, '%Y-%m') AS mon,
         SUM(num) AS cnt
     FROM resume_info
     WHERE YEAR(date) = 2025
     GROUP BY job, mon
     ORDER BY mon DESC, cnt DESC) d1
LEFT JOIN
    (SELECT
         job,
         DATE_FORMAT(date, '%Y-%m') AS mon,
         SUM(num) AS cnt
     FROM resume_info
     WHERE YEAR(date) = 2026
     GROUP BY job, mon
     ORDER BY mon DESC, cnt DESC) d2
on d1.job = d2.job and right(d1.mon,2)=right(d2.mon,2)

order by d1.mon desc,d1.job desc;