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

本题思路

1. 主要问题是在时间的处理， date_format(date, '%Y-%m')可以取出年月，而year(date) 和month(date)可以分别取出年、月方便join是过滤

select
a.job as job,
a.day as first_year_mon,
a.cnt as first_year_cnt,
b.day as second_year_mon,
b.cnt as second_year_cnt
from
  (
    select
      a.job as job,
      a.day as day,
      a.y as y,
      a.m as m,
      sum(a.num) as cnt
    from
      (
        select
          job,
          date_format(date, '%Y-%m') as day,
          year(date) as y,
          month(date) as m,
          num
        from
          resume_info
      ) a
    group by
      a.job,
      a.y,
      a.m,
      a.day
  ) a
  left join (
    select
      a.job as job,
      a.day as day,
      a.y as y,
      a.m as m,
      sum(a.num) as cnt
    from
      (
        select
          job,
          date_format(date, '%Y-%m') as day,
          year(date) as y,
          month(date) as m,
          num
        from
          resume_info
      ) a
    group by
      a.job,
      a.y,
      a.m,
      a.day
  ) b on a.job = b.job
  and a.y+1 = b.y and a.m = b.m
  where b.job is not null AND a.y = 2025
  order by a.day desc,a.job desc
;