题解 |#实习广场投递简历分析（三）#

解题思路：

1. 表连接：把2025年和2026年需要的数据分别算出来，
2. 连接条件：job相同，月份相同（ps:月份需要我们单独截取出来）

细节部分：

1. 表中出现了“年—月”，也就是告诉我们需要用到substr(start, length)。start 起始部分，length 截取长度
2. 另外一点我们还需要把2025年和2026年的月份分别截取出来，因为后面两个表的连接条件需要用月份去一一对应（ps: 我们希望 Python 2025-01 对应 Python 2026-01 但实际上用job相同这个条件：Python 2025-01还会去对应Python 2026-02。因为没有月份的限制所以会出现这种情况）

把2025年需要的信息找出来

select job, substr(date, 1, 7) as mon, substr(date, 6,2) as M1, sum(num) as cnt
from resume_info
where year(date) = 2025
group by job, mon
order by mon desc

把2026年需要的信息找出来

select job, substr(date, 1, 7) as mon, substr(date, 6,2) as M2, sum(num) as cnt
from resume_info
where year(date) = 2026
group by job, mon
order by mon desc

把两张表连接起来

(select job, substr(date, 1, 7) as mon, substr(date, 6,2) as M1, sum(num) as cnt
from resume_info
where year(date) = 2025
group by job, mon
order by mon desc) as a #2025年我们需要的信息
 
join
 
(select job, substr(date, 1, 7) as mon, substr(date, 6,2) as M2, sum(num) as cnt
from resume_info
where year(date) = 2026
group by job, mon
order by mon desc) as b  #2026年我们需要的信息

最后就这样，代码有点儿多，但其实思路很简单

select a.job,
    a.mon as first_year_mon,
    a.cnt as first_year_cnt,
    b.mon as second_year_mon,
    b.cnt as second_year_cnt
from 
(select job, substr(date, 1, 7) as mon, substr(date, 6,2) as M1, sum(num) as cnt
from resume_info
where year(date) = 2025
group by job, mon
order by mon desc) as a
 
join
 
(select job, substr(date, 1, 7) as mon, substr(date, 6,2) as M2, sum(num) as cnt
from resume_info
where year(date) = 2026
group by job, mon
order by mon desc) as b

on a.job = b.job and a.M1 = b.M2

order by first_year_mon desc, a.job desc;