题解 | #实习广场投递简历分析(三)#
实习广场投递简历分析(三)
http://www.nowcoder.com/practice/83f84aa5c32b4cf5a75558d02dd7743c
SELECT a.job, a.mon first_year_mon, a.cnt first_year_cnt,b.mon second_year_mon,b.cnt second_year_cnt
FROM
(SELECT job, DATE_FORMAT(date
,'%Y-%m') mon , SUM(num) cnt,DATE_FORMAT(date
,'%m') a
FROM resume_info
WHERE YEAR(date
) =2025
GROUP BY job,mon,a) a
LEFT JOIN
(SELECT job, DATE_FORMAT(DATE
,'%Y-%m') mon , SUM(num) cnt,DATE_FORMAT(DATE
,'%m') b
FROM resume_info
WHERE YEAR(DATE
) =2026
GROUP BY job,mon,b) b
ON a.job = b.job AND a.a =b.b
ORDER BY first_year_mon DESC, a.job DESC