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

# # 1,t1,查询在2025年投递简历的每个岗位，每一个月内收到简历的数目
# select job,
# date_format(date, '%Y-%m') as first_year_mon,
# date_format(date, '%m') as first_mon,
# sum(num) as first_year_cnt
# from resume_info
# where date like '2025%'
# group by job, first_year_mon, first_mon
# order by first_year_mon desc, job desc

# # 2，t2，查询对应的2026年的同一个月同岗位，收到简历的数目
# select job,
# date_format(date, '%Y-%m') as second_year_mon,
# date_format(date, '%m') as second_mon,
# sum(num) as second_year_cnt
# from resume_info
# where date like '2026%'
# group by job, second_year_mon, second_mon
# order by second_year_mon desc, job desc

# 3,t3,外连接
select t1.job, first_year_mon, first_year_cnt, second_year_mon, second_year_cnt
from (
    # 1,t1,查询在2025年投递简历的每个岗位，每一个月内收到简历的数目
    select job,
    date_format(date, '%Y-%m') as first_year_mon,
    date_format(date, '%m') as first_mon,
    sum(num) as first_year_cnt
    from resume_info
    where date like '2025%'
    group by job, first_year_mon, first_mon
    order by first_year_mon desc, job desc) as t1
left join (
    # 2，t2，查询对应的2026年的同一个月同岗位，收到简历的数目
    select job,
    date_format(date, '%Y-%m') as second_year_mon,
    date_format(date, '%m') as second_mon,
    sum(num) as second_year_cnt
    from resume_info
    where date like '2026%'
    group by job, second_year_mon, second_mon
    order by second_year_mon desc, job desc) as t2
on t1.job = t2.job and t1.first_mon = t2.second_mon;