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

SELECT 
first_table.job,
first_table.first_year_mon,
first_table.first_year_cnt,
second_table.second_year_mon,
second_table.second_year_cnt
FROM(
    SELECT
    job,
    DATE_FORMAT(date,'%Y-%m') first_year_mon,
    SUM(num) first_year_cnt
    FROM resume_info
    WHERE date BETWEEN '2025-01-01' AND '2025-12-31'
    GROUP BY job,DATE_FORMAT(date,'%Y-%m')) first_table
INNER JOIN 
(
    SELECT
    job,
    DATE_FORMAT(date,'%Y-%m') second_year_mon,
    SUM(num) second_year_cnt
    FROM resume_info
    WHERE date BETWEEN '2026-01-01' AND '2026-12-31'
    GROUP BY job,DATE_FORMAT(date,'%Y-%m')
) second_table
ON first_table.job = second_table.job
AND right(first_table.first_year_mon,2) = right(second_table.second_year_mon,2)
ORDER BY first_table.first_year_mon DESC,first_table.job DESC