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

• 参考答案

select s1.job,
left(s1.date,7) as 'first_year_mon',
s1.cnt as'first_year_cnt',
left(s2.date,7) as 'second_year_mon',
s2.cnt as 'second_year_cnt'
from (
    select job, date,sum(num) cnt from resume_info
    where year(date) = '2025'
    group by job ,month(date)) s1
join (
    select job, date,sum(num) cnt from resume_info
    where year(date) = '2026'
    group by job ,month(date)) s2
on 
    s1.job = s2.job and
    month(s1.date) = month(s2.date)
order by first_year_mon desc ,s1.job desc

• 本题解析
  本题难点在于
  1.日期截取和日期的函数的灵活应用
  2.按照job和月份同时进行分组
  3.将两个分组好的查询数据集通过job和月份拼接到一起 4.一个order里面出现多个条件