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

/*
SELECT job, DATE_FORMAT(date, "%y-%m")
FROM resume_info as info
WHERE YEAR(date) = 2025
GROUP BY MONTH(date), job

SELECT job, DATE_FORMAT(date, "%y-%m")
FROM resume_info as info
WHERE YEAR(date) = 2026
GROUP BY MONTH(date), job
*/
SELECT t2025.job, t2025.dat as first_year_mon, t2025.s as first_year_cnt,
t2026.dat as second_year_mon, t2026.s as second_year_cnt
FROM (SELECT job, DATE_FORMAT(date, "%Y-%m") as dat, sum(num) as s, info.date
FROM resume_info as info
WHERE YEAR(date) = 2025
GROUP BY MONTH(date), job) as t2025
JOIN (SELECT job, DATE_FORMAT(date, "%Y-%m") as dat, sum(num) as s, info.date
FROM resume_info as info
WHERE YEAR(date) = 2026
GROUP BY MONTH(date), job) as t2026
on t2025.job = t2026.job
and MONTH(t2025.date) = MONTH(t2026.date)
ORDER BY t2025.dat DESC, t2025.job DESC;