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

WITH 
    t1 AS (
        SELECT 
            job,
            DATE_FORMAT(date, '%Y-%m') AS first_year_mon,
            SUM(num) AS first_year_cnt,
            DATE_FORMAT(date, '%m') AS month1
        FROM 
            resume_info
        WHERE 
            YEAR(date) = 2025
        GROUP BY 
            job, 
            DATE_FORMAT(date, '%Y-%m'),  -- 确保所有非聚合列都在GROUP BY中
            DATE_FORMAT(date, '%m')      -- 添加month1对应的表达式
    ),
    t2 AS (
        SELECT 
            job,
            DATE_FORMAT(date, '%Y-%m') AS second_year_mon,
            SUM(num) AS second_year_cnt,
            DATE_FORMAT(date, '%m') AS month2
        FROM 
            resume_info
        WHERE 
            YEAR(date) = 2026
        GROUP BY 
            job, 
            DATE_FORMAT(date, '%Y-%m'),  -- 确保所有非聚合列都在GROUP BY中
            DATE_FORMAT(date, '%m')      -- 添加month2对应的表达式
    )
SELECT 
    t1.job,
    t1.first_year_mon,
    t1.first_year_cnt,
    t2.second_year_mon,
    t2.second_year_cnt
FROM 
    t1
    LEFT JOIN t2 
        ON t1.job = t2.job 
        AND t1.month1 = t2.month2
    order by first_year_mon desc, t1.job desc;