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

第一步根据筛选条件，特别是date_format(date,'%y-%m')创造满条件的2025的月度简历情况，即
 (
        SELECT
            job,
            DATE_FORMAT(date, '%Y-%m') AS mon,
            SUM(num) AS f
        FROM
            resume_info
        WHERE
            date BETWEEN '2025-01-01' AND '2025-12-31'
        GROUP BY mon, job
    ) AS a
第二步，仿照第一步同样生成2026年的月度简历情况，即
 (
        SELECT
            job,
            DATE_FORMAT(date, '%Y-%m') AS mon,
            SUM(num) AS s
        FROM
            resume_info
        WHERE
            date BETWEEN '2026-01-01' AND '2026-12-31'
        GROUP BY mon, job
    ) AS b
第三步，将以上两步生成的2025的a表与2026的b表用left join连接，然后用SELECT选出  a.job,   a.mon as first_year_mon,  a.f AS first_year_cnt,  b.mon as second_year_mon,b.s AS second_year_cnt，并且排序ORDER BY  a.mon DESC,a.job DESC;
最后也是最难的就是，如何将2026的月份和2025的月份对应上，我百度的结果：a left join b ON a.job = b.job ----AND DATE_FORMAT(DATE_ADD(STR_TO_DATE(CONCAT(a.mon, '-01'), '%Y-%m-%d'), INTERVAL 1 YEAR), '%Y-%m') = b.mon，即完整代码：
   SELECT
    a.job,
    a.mon as first_year_mon,
    a.f AS first_year_cnt,
    b.mon as second_year_mon,
    b.s AS second_year_cnt
FROM
    (
        SELECT
            job,
            DATE_FORMAT(date, '%Y-%m') AS mon,
            SUM(num) AS f
        FROM
            resume_info
        WHERE
            date BETWEEN '2025-01-01' AND '2025-12-31'
        GROUP BY mon, job
    ) AS a
LEFT JOIN
    (
        SELECT
            job,
            DATE_FORMAT(date, '%Y-%m') AS mon,
            SUM(num) AS s
        FROM
            resume_info
        WHERE
            date BETWEEN '2026-01-01' AND '2026-12-31'
        GROUP BY mon, job
    ) AS b
    ON a.job = b.job AND DATE_FORMAT(DATE_ADD(STR_TO_DATE(CONCAT(a.mon, '-01'), '%Y-%m-%d'), INTERVAL 1 YEAR), '%Y-%m') = b.mon
ORDER BY
    a.mon DESC,
    a.job DESC;