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

-- 2025年的
select job,date_format(r.date,'%Y-%m'),sum(num) from resume_info r
where r.date<'2026-01-01' and r.date>='2025-01-01'
group by job,date_format(r.date,'%Y-%m');

-- 2026年的
select job,date_format(r.date,'%Y-%m'),sum(num) from resume_info r
where r.date<'2027-01-01' and r.date>='2026-01-01'
group by job,date_format(r.date,'%Y-%m');
-- 答案
select tmp1.job,tmp1.year_mon first_year_mon,tmp1.year_cnt first_year_cnt,tmp2.year_mon second_year_mon,
tmp2.year_cnt second_year_cnt
from (select job,date_format(r.date,'%Y-%m') year_mon,sum(num) year_cnt from resume_info r
      where r.date<'2026-01-01' and r.date>='2025-01-01'
      group by job,date_format(r.date,'%Y-%m')) tmp1,
     (select job,date_format(r.date,'%Y-%m') year_mon,sum(num) year_cnt from resume_info r
      where r.date<'2027-01-01' and r.date>='2026-01-01'
      group by job,date_format(r.date,'%Y-%m')) tmp2
where tmp1.job = tmp2.job
and substr(tmp1.year_mon,6,7)  = substr(tmp2.year_mon,6,7)
order by tmp1.year_mon desc,tmp1.job desc;