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

select 
  t1.job,
  first_year_mon,
  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,
      first_year_mon
  ) as t1
cross 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,
      second_year_mon
  ) as t2
on 
  t1.job=t2.job and right(t1.first_year_mon,2)=right(t2.second_year_mon,2)
order by 
  first_year_mon desc,
  t1.job desc;