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

sql script

-- 2025
select job, substring(date, 1, 7) as mon, sum(num) as cnt
from resume_info
where date like '2025%'
group by job,substring(date, 1, 7)


-- 2026
select job, substring(date, 1, 7) as mon, sum(num) as cnt
from resume_info
where date like '2026%'
group by job,substring(date, 1, 7)



select tmp1.job, tmp1.mon as first_year_mon, tmp1.cnt as first_year_cnt,
tmp2.mon as second_year_mon, tmp2.cnt as second_year_cnt
from (select job, substring(date, 1, 7) as mon, sum(num) as cnt
      from resume_info
      where date like '2025%'
      group by job,substring(date, 1, 7)) tmp1,
      (select job, substring(date, 1, 7) as mon, sum(num) as cnt
       from resume_info
       where date like '2026%'
       group by job,substring(date, 1, 7)) tmp2
where tmp1.job=tmp2.job
and substring(tmp1.mon,6,7)=substring(tmp2.mon,6,7)
order by first_year_mon desc, tmp1.job desc