在牛客实习广场有很多公司开放职位给同学们投递,同学投递完就会把简历信息存到数据库里。 现在有简历信息表(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
示例1

输入

drop table if exists resume_info;
CREATE TABLE resume_info (
id int(4) NOT NULL,
job varchar(64) NOT NULL,
date date NOT NULL,
num int(11) NOT NULL,
PRIMARY KEY (id));

INSERT INTO resume_info VALUES
(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);

输出

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
加载中...