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

WITH f AS(select job,DATE_FORMAT(date,'%Y-%m') first_year_mon,sum(num) first_year_cnt
         from resume_info
          where year(date)=2025
         group by job,month(date)),
     s AS(select job,DATE_FORMAT(date,'%Y-%m') second_year_mon,sum(num) second_year_cnt
         from resume_info
          where year(date)=2026
         group by job,month(date))
SELECT f.*,second_year_mon,second_year_cnt
FROM f JOIN s ON f.job=s.job and SUBSTR(first_year_mon,6,7)=SUBSTR(second_year_mon,6,7)
ORDER BY first_year_mon DESC,job desc

DATE_FORMAT返回的是字符串，在两表连接那里就只能用substr而不能用month了