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

SELECT t1.job, first_year_mon, first_year_cnt, second_year_mon, second_year_cnt
FROM
(SELECT job, 
 LEFT(date, 7) AS 'first_year_mon', 
 SUM(num) AS 'first_year_cnt',
 EXTRACT(MONTH FROM date) AS 'mon'
FROM resume_info
WHERE date LIKE '2025%'
GROUP BY job, first_year_mon
ORDER BY date DESC, job DESC) t1,
(SELECT job, 
 LEFT(date, 7) AS 'second_year_mon', 
 SUM(num) AS 'second_year_cnt',
 EXTRACT(MONTH FROM date) AS 'mon'
FROM resume_info
WHERE date LIKE '2026%'
GROUP BY job, second_year_mon
ORDER BY date DESC, job DESC) t2
WHERE t1.job = t2.job AND t1.mon = t2.mon
ORDER BY first_year_mon DESC, t1.job DESC;