实习广场投递简历分析（二、三）

题目1:

在牛客实习广场有很多公司开放职位给同学们投递，同学投递完就会把简历信息存到数据库里。

现在有简历信息表(resume_info)，部分信息简况如下:

id	job	date	num
1	C++	2025-01-02	53
2	Python	2025-01-02	23
3	Java	2025-01-02	12
4	C++	2025-01-03	54
5	Python	2025-01-03	43
6	Java	2025-01-03	41
7	Java	2025-02-03	24
8	C++	2025-02-03	23
9	Python	2025-02-03	34
10	Java	2025-02-04	42
11	C++	2025-02-04	45
12	Python	2025-02-04	59
13	Python	2025-03-04	54
14	C++	2025-03-04	65
15	Java	2025-03-04	92
16	Python	2025-03-05	34
17	C++	2025-03-05	34
18	Java	2025-03-05	34
19	Python	2026-01-04	230
20	C++	2026-02-06	231

第1行表示，在2025年1月2号，C++岗位收到了53封简历

。。。

最后1行表示，在2026年2月6号，C++岗位收到了231封简历

 

请你写出SQL语句查询在2025年内投递简历的每个岗位，每一个月内收到简历的数量，并且按先按月份降序排序，再按简历数目降序排序，以上例子查询结果如下:

job	mon	cnt
Java	2025-03	126
C++	2025-03	99
Python	2025-03	88
Python	2025-02	93
C++	2025-02	68
Java	2025-02	66
C++	2025-01	107
Python	2025-01	66
Java	2025-01	53

#方法一
#select job,mon,sum(num) as cnt
#from
# (
# select job,date_format(date,"%Y-%m") as mon,num
# from resume_info
# where year(date) = 2025
# ) t

#group by job,mon
#order by mon desc,cnt desc

-- 方法二
select job,date_format(date,'%Y-%m') as mon,sum(num) as cnt
from resume_info
where date like '2025%' -- 符合最左前缀匹配原则，也走索引
group by job,mon
order by mon desc,cnt desc;

注意：关键一点对于别名的应用group by，having，order可以使用，但是where不可以，虽然select 执行顺序是最晚的

参考：https://dev.mysql.com/doc/refman/8.0/en/problems-with-alias.html

题目2：

在牛客实习广场有很多公司开放职位给同学们投递，同学投递完就会把简历信息存到数据库里。

现在有简历信息表(resume_info)，部分信息简况如下:

id	job	date	num
1	C++	2025-01-02	53
2	Python	2025-01-02	23
3	Java	2025-01-02	12
4	C++	2025-01-03	54
5	Python	2025-01-03	43
6	Java	2025-01-03	41
7	Java	2025-02-03	24
8	C++	2025-02-03	23
9	Python	2025-02-03	34
10	Java	2025-02-04	42
11	C++	2025-02-04	45
12	Python	2025-02-04	59
13	C++	2026-01-04	230
14	Java	2026-01-04	764
15	Python	2026-01-04	644
16	C++	2026-01-06	240
17	Java	2026-01-06	714
18	Python	2026-01-06	624
19	C++	2026-01-04	260
20	Java	2026-02-14	721
21	Python	2026-02-14	321
22	C++	2026-02-14	134
23	Java	2026-02-24	928
24	Python	2026-02-24	525
25	C++	2027-02-06	231

 

第1行表示，在2025年1月2号，C++岗位收到了53封简历

......

最后1行表示，在2027年2月6号，C++岗位收到了231封简历

请你写出SQL语句查询在2025年投递简历的每个岗位，每一个月内收到简历的数目，和对应的2026年的同一个月同岗位，收到简历的数目，最后的结果先按first_year_mon月份降序，再按job降序排序显示，以上例子查询结果如下:

job	first_year_mon	first_year_cnt	second_year_mon	second_year_cnt
Python	2025-02	93	2026-02	846
Java	2025-02	66	2026-02	1649
C++	2025-02	68	2026-02	394
Python	2025-01	66	2026-01	1268
Java	2025-01	53	2026-01	1478
C++	2025-01	107	2026-01	470

解析:

第1行表示Python岗位在2025年2月收到了93份简历，在对应的2026年2月收到了846份简历

......

最后1行表示C++岗位在2025年1月收到了107份简历，在对应的2026年1月收到了470份简历

方法一：

select a.job,first_year_mon,first_year_cnt,second_year_mon,second_year_cnt
from
　　(select job,
　　date_format(date,'%Y-%m') as second_year_mon,
　　sum(num) as second_year_cnt
　　from
　　resume_info ri
　　where year(date)=2026
　　group by job,second_year_mon
　　) b
join
　　(
　　select job,
　　date_format(date,'%Y-%m') as first_year_mon,
　　sum(num) as first_year_cnt
　　from
　　resume_info ri
　　where year(date)=2025
　　group by job,first_year_mon
　　) a
on a.job = b.job and right(first_year_mon,2)=right(second_year_mon,2)
order by first_year_mon desc,job desc

注意：形如：DATE_ADD(t1.first_year_mon,interval 1 YEAR)=t2.second_year_mon MONTH(first_year_mon)= month(second_year_mon) 均错误，因为

month,year,date_format只对完整的时间数据格式有用，并且需要注意是日期格式还是字符串格式

 

方法二：添加新字段

select a.job, a.first_year_mon, a.first_year_cnt , b.second_year_mon, b.second_year_cnt
from
　　(select job ,
　　concat(job,month(date)) as id ,
　　DATE_FORMAT(date , '%Y-%m') first_year_mon ,
　　sum(num) first_year_cnt
　　from resume_info
　　where date < '2026-01-01'and date > '2024-12-31'
　　group by job, first_year_mon)a
join
　　(select job ,
　　concat(job,month(date)) as id ,
　　DATE_FORMAT(date, '%Y-%m') second_year_mon ,
　　sum(num) second_year_cnt
　　from resume_info
　　where date < '2027-01-01'and date > '2025-12-31'
　　group by job, second_year_mon)b
on a.id = b.id
order by first_year_mon desc , job desc;

 

方法三：

select a.job,a.first_year_mon,a.num2026,b.second_year_mon,b.num2026
from
(select job,date_format(date,'%Y-%m') as first_year_mon,sum(num) as num2026
from resume_info
where year(date)='2025'
group by date_format(date,'%Y-%m'),job) as a
join
(select job,date_format(date,'%Y-%m') as second_year_mon,sum(num) as num2026
from resume_info
where year(date)='2026'
group by date_format(date,'%Y-%m'),job) as b
on a.job=b.job
and replace(a.first_year_mon,'2025','2026')=b.second_year_mon
order by a.first_year_mon desc,a.job desc;

本上的差别就是对时间格式的处理方法不同