题解 | #实习广场投递简历分析(三)#
实习广场投递简历分析(三)
http://www.nowcoder.com/practice/83f84aa5c32b4cf5a75558d02dd7743c
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;
