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

SELECT t1.job, first_year_mon, first_year_cnt, second_year_mon, second_year_cnt
FROM
(SELECT job, SUBSTR(date,1,7) AS first_year_mon, SUM(num) AS first_year_cnt
FROM resume_info
WHERE DATE LIKE '2025%'
GROUP BY job, first_year_mon) AS t1
JOIN
(SELECT job, SUBSTR(date,1,7) AS second_year_mon, SUM(num) AS second_year_cnt
FROM resume_info
WHERE DATE LIKE '2026%'
GROUP BY job, second_year_mon) AS t2 
ON t1.job = t2.job AND substr(first_year_mon,-2)= substr(second_year_mon,-2)
ORDER BY first_year_mon DESC, t1.job DESC