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

SELECT r1.job, r1.first_year_mon, r1.first_year_cnt, r2.second_year_mon, r2.second_year_cnt
FROM
( SELECT job, LEFT(date, 7) AS first_year_mon, SUM(num) AS first_year_cnt,
MONTH(date) AS mon
FROM resume_info
WHERE date LIKE '2025%'
GROUP BY job, MONTH(date)
ORDER BY MONTH(date) DESC, job DESC) r1
LEFT JOIN
( SELECT r.job, LEFT(r.date, 7) AS second_year_mon, SUM(r.num) AS second_year_cnt,
MONTH(r.date) AS mon
FROM resume_info r
WHERE r.date LIKE '2026%'
GROUP BY r.job, MONTH(r.date)
ORDER BY MONTH(r.date) DESC, r.job DESC) r2
ON r1.job=r2.job
AND r1.mon=r2.mon;
代码已通过。
思路是：两个表联结。一个表的信息是2025年各月、各job和数量，另一个表是2026年各月、各job和数量，两个表通过月份相同的字段和相同的job字段联结，就生成所需要的表了。