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

WITH t1 AS(
SELECT
job,
LEFT(date, 7) AS first_year_mon,
SUM(num) AS first_year_cnt

FROM resume_info

WHERE YEAR(date) = '2025'

GROUP BY job, LEFT(date, 7)),

t2 AS(
SELECT
job,
LEFT(date, 7) AS second_year_mon,
SUM(num) AS second_year_cnt

FROM resume_info

WHERE YEAR(date) = '2026'

GROUP BY job, LEFT(date, 7))

SELECT
t1.job,
first_year_mon,
first_year_cnt,
second_year_mon,
second_year_cnt

FROM t1
JOIN t2
    ON t1.job = t2. job AND SUBSTRING(first_year_mon, 6, 2) = SUBSTRING(second_year_mon, 6, 2)
    
ORDER BY first_year_mon DESC, t1.job DESC