题目描述: 1. 简历信息表resume_info如图1,包括岗位名称job,投递时间date,投递数量num三个字段。第1行表示,在2025年1月2号,C++岗位收到了53封简历。
2.请通过SQL查询: 2.1.2025年每个月内每个岗位收到简历的数量和,如图2。
2.2.2026年每个月内每个岗位收到简历的数量和,如图3。
2.3.按照相同月份连接2.1和2.2中的两表,并按照date和job降序排序,如图4。
欢迎大家转发,一起传播知识和正能量,帮助到更多人。辛苦大家转发时注明出处(也是咱们公益编程交流群的入口网址),刘经纬老师共享知识相关文件下载地址为:http://liujingwei.cn
解题思路:
1.查询:2025年每个月内每个岗位收到简历的数量和,如图2,代码如下:
select job,date,sum(num) as cnt
from resume_info where year(date) = "2025"
group by job, month(date);
2.查询:2026年每个月内每个岗位收到简历的数量和,如图3,代码如下:
select job,date,sum(num) as cnt
from resume_info where year(date) = "2026"
group by job, month(date);
3.组合查询:按照相同月份连接2.1和2.2中的两表,并按照date和job降序排序,如图4,代码如下:
select r1.job, date_format(r1.date, "%Y-%m") as date2025,
r1.cnt as cnt2025,
date_format(r2.date, "%Y-%m") as date2026,
r2.cnt as cnt2026
from (
select job, date, sum(num) as cnt
from resume_info where year(date) = "2025"
group by job, month(date)) as r1
inner join (
select job, date, sum(num) as cnt
from resume_info where year(date) = "2026"
group by job, month(date)) as r2 on month(r1.date) = month(r2.date) and r1.job = r2.job order by date2025 desc, r1.job desc;
温馨期待: 期待大家提出宝贵建议,互相交流,收获更大[太阳]