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

WITH t AS (
    SELECT
        job,
        YEAR(date) year,
        MONTH(date) month,
        SUM(num) cnt
    FROM
        resume_info
    GROUP BY
        job,year,month
)
SELECT
    t1.job,
    CONCAT(t1.year,'-',LPAD(t1.month,2,'0')) first_year_mon,
    t1.cnt first_year_cnt,
    CONCAT(t2.year,'-',LPAD(t2.month,2,'0')) second_year_mon,
    t2.cnt second_year_cnt
FROM
    t t1
INNER JOIN
    t t2 ON t1.job=t2.job AND t1.year=2025 AND t2.year=2026 AND t1.month=t2.month
ORDER BY
    t1.month DESC,
    job DESC