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

SELECT b2.job, b2.first_year_mon, b2.cnt AS first_year_cnt,
b2.second_year_mon, b3.second_year_cnt
FROM (
    SELECT b1.job,
    DATE_FORMAT(b1.first_year_mon,'%Y-%m') AS first_year_mon, 
    DATE_FORMAT(b1.second_year_mon,'%Y-%m') AS second_year_mon,
    SUM(b1.num) AS cnt
    FROM (
        SELECT job, date AS first_year_mon, 
        DATE_ADD(date, INTERVAL 1 YEAR) AS second_year_mon, num
        FROM resume_info
        ) AS b1
    GROUP BY b1.job, DATE_FORMAT(b1.first_year_mon,'%Y-%m') , DATE_FORMAT(b1.second_year_mon,'%Y-%m')) AS b2

JOIN (
    SELECT DATE_FORMAT(date,'%Y-%m') AS date, SUM(num) AS second_year_cnt, job
    FROM resume_info
    GROUP BY job, DATE_FORMAT(date,'%Y-%m')
) AS b3
ON b2.second_year_mon=b3.date
WHERE b2.job=b3.job AND b2.first_year_mon LIKE '2025%'
ORDER BY b2.first_year_mon DESC, b2.job DESC