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

单独算出2025和2026年每个月份的投递数量并不难，上一题中也要做过了，
难点在于怎样将相同job不同年相同月的链接起来，这里可以考虑几种字符串提取函数

SELECT a.job,
       a.first_year_mon,
       a.first_year_cnt,
       second_year_mon,
       second_year_cnt
FROM
  (SELECT job,
          date_format(date,'%Y-%m') AS first_year_mon,
          sum(num) AS first_year_cnt
   FROM resume_info
   WHERE year(date)='2025'
   GROUP BY job,
            date_format(date,'%Y-%m')) a
JOIN
  (SELECT job,
          date_format(date,'%Y-%m') AS second_year_mon,
          sum(num) AS second_year_cnt
   FROM resume_info
   WHERE year(date)='2026'
   GROUP BY job,
            date_format(date,'%Y-%m')) b ON a.job=b.job
AND left(a.first_year_mon,4)=left(b.second_year_mon,4)-1 -- 年份差1
AND substr(a.first_year_mon,6,2)=substr(b.second_year_mon,6,2) -- 月份相同
ORDER BY first_year_mon DESC,
         job DESC