牛客网SQL训练4—SQL进阶挑战_牛客网 sql进阶

---

一、增删改操作

1. 插入记录

【题目1：插入记录 (一)】

--输入：
drop table if EXISTS exam_record;
CREATE TABLE IF NOT EXISTS exam_record (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
exam_id int NOT NULL COMMENT '试卷ID',
start_time datetime NOT NULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
TRUNCATE exam_record;
1
2
3
4
5
6
7
8
9
10
11

插入记录的方式汇总：
1. 普通插入（全字段）：INSERT INTO table_name VALUES (value1, value2, …)
 普通插入（限定字段）：INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …)
 多条一次性插入：INSERT INTO table_name (column1, column2, …) VALUES (value1_1, value1_2, …), (value2_1, value2_2, …), …
2. 从另一个表导入：INSERT INTO table_name SELECT * FROM table_name2 [WHERE key=value]
3. 带更新的插入：REPLACE INTO table_name VALUES (value1, value2, …) （注意这种原理是检测到主键或唯一性索引键重复就删除原记录后重新插入）

-- 因为id为自增，所以插入两条数据除自增id列以外的列
-- 提交时间可以让它自己计算
insert into exam_record(uid,exam_id,start_time,submit_time,score) values
(1001,9001,'2021-09-01 22:11:12','2021-09-01 22:11:12'+interval 50 minute,90),
(1002,9002,'2021-09-04 07:01:02',null,null)
;
1
2
3
4
5
6

【题目2：插入记录 (二)】

--输入：
drop table if EXISTS exam_record;
CREATE TABLE IF NOT EXISTS exam_record (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
exam_id int NOT NULL COMMENT '试卷ID',
start_time datetime NOT NULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE TABLE IF NOT EXISTS exam_record_before_2021 (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
exam_id int NOT NULL COMMENT '试卷ID',
start_time datetime NOT NULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
TRUNCATE exam_record;
TRUNCATE exam_record_before_2021;
INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-01 09:00:01', null, null),
(1001, 9002, '2020-01-02 09:01:01', '2020-01-02 09:21:01', 70),
(1001, 9002, '2020-09-02 09:00:01', null, null),
(1002, 9001, '2021-05-02 10:01:01', '2021-05-02 10:30:01', 81),
(1002, 9002, '2021-09-02 12:01:01', null, null);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

-- 第一列为自增主键列，不能直接复制过去
-- 只复制2021年之前的记录 & 只复制已完成了的试题作答纪录：用提交时间
insert into exam_record_before_2021(uid,exam_id,start_time,submit_time,score)
select uid,exam_id,start_time,submit_time,score from exam_record
where substr(submit_time,1,4)<='2021'
;
1
2
3
4
5
6

【题目3：插入记录 (三)】

--输入：
drop table if EXISTS examination_info;
CREATE TABLE IF NOT EXISTS examination_info (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
tag varchar(32) COMMENT '类别标签',
difficulty varchar(8) COMMENT '难度',
duration int NOT NULL COMMENT '时长(分钟数)',
release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_bin;
TRUNCATE examination_info;
INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
(9001, 'SQL', 'hard', 60, '2020-01-01 10:00:00'),
(9002, '算法', 'easy', 60, '2020-01-01 10:00:00'),
(9003, 'SQL', 'medium', 60, '2020-01-02 10:00:00'),
(9004, '算法', 'hard', 80, '2020-01-01 10:00:00');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

replace into examination_info(exam_id,tag,difficulty,duration,release_time) values
(9003,'SQL','hard',90,'2021-01-01 00:00:00')
;
1
2
3

2. 更新记录

【题目1：更新记录 (一)】

--输入：
drop table if EXISTS examination_info;
CREATE TABLE IF NOT EXISTS examination_info (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
tag varchar(32) COMMENT '类别标签',
difficulty varchar(8) COMMENT '难度',
duration int NOT NULL COMMENT '时长',
release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_bin;
TRUNCATE examination_info;
INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
(9001, 'SQL', 'hard', 60, '2020-01-01 10:00:00'),
(9002, 'python', 'easy', 60, '2020-01-01 10:00:00'),
(9003, 'Python', 'medium', 80, '2020-01-01 10:00:00'),
(9004, 'PYTHON', 'hard', 80, '2020-01-01 10:00:00');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

修改记录的方式汇总：
1. 设置为新值：UPDATE table_name SET column_name=new_value [, column_name2=new_value2] [WHERE column_name3=value3]
2. 根据已有值替换：UPDATE table_name SET key1=replace(key1, ‘查找内容’, ‘替换成内容’) [WHERE column_name3=value3]

-- 方法1
update examination_info
set tag='Python'
where tag='PYTHON'
;
-- 方法2
update examination_info
set tag=replace(tag,'PYTHON','Python')
where tag='PYTHON'
;

-- 思维扩展：第二种方式不仅可用于整体替换，还能做子串替换，例如要实现将tag中所有的PYTHON替换为Python（如CPYTHON=>CPython），可写作：
update examination_info
set tag = replace(tag, "PYTHON", "Python")
where tag like "%PYTHON%";
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

【题目2：更新记录 (二)】

--输入：
drop table if EXISTS exam_record;
CREATE TABLE IF NOT EXISTS exam_record (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
exam_id int NOT NULL COMMENT '试卷ID',
start_time datetime NOT NULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:01', 80),
(1001, 9002, '2021-09-01 09:01:01', '2021-09-01 09:21:01', 90),
(1002, 9001, '2021-08-02 19:01:01', null, null),
(1002, 9002, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89),
(1003, 9001, '2021-09-02 12:01:01', null, null),
(1003, 9002, '2021-09-01 12:01:01', null, null);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

-- 未完成：分数为null
update exam_record
set submit_time='2099-01-01 00:00:00',score=0
where substr(start_time,1,10)<'2021-09-01' and score is null;
;
1
2
3
4
5

3. 删除记录

【题目1：删除记录 (一)】

--输入：
drop table if EXISTS exam_record;
CREATE TABLE IF NOT EXISTS exam_record (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
exam_id int NOT NULL COMMENT '试卷ID',
start_time datetime NOT NULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
TRUNCATE exam_record;
INSERT INTO exam_record(uid, exam_id, start_time, submit_time, score) VALUES
(1001, 9001, '2020-01-01 22:11:12', '2020-01-01 23:16:12', 50),
(1001, 9002, '2020-01-02 09:01:01', '2020-01-02 09:06:00', 58),
(1002, 9001, '2021-05-02 10:01:01', '2021-05-02 10:05:58', 60),
(1002, 9002, '2021-06-02 19:01:01', '2021-06-02 19:05:01', 54),
(1003, 9001, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 49),
(1003, 9001, '2021-09-05 19:01:01', '2021-09-05 19:15:01', 70),
(1003, 9001, '2021-09-06 19:01:01', '2021-09-06 19:05:01', 80),
(1003, 9002, '2021-09-09 07:01:02', null, null);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

删除记录的方式汇总：
1. 根据条件删除：DELETE FROM tb_name [WHERE options] [ [ ORDER BY fields ] LIMIT n ]
2. 全部删除（表清空，包含自增计数器重置）：TRUNCATE TABLE tb_name

-- 时间差：TIMESTAMPDIFF(interval, time_start, time_end)可计算time_start-time_end的时间差，单位以指定的interval为准，
-- 常用可选：SECOND 秒
-- 		 MINUTE 分钟（返回秒数差除以60的整数部分）
-- 		 HOUR 小时（返回秒数差除以3600的整数部分）
-- 		 DAY 天数（返回秒数差除以3600*24的整数部分）
-- 		 MONTH 月数
-- 		 YEAR 年数

delete from exam_record
where timestampdiff(minute,start_time,submit_time)<5 and score<60
;
1
2
3
4
5
6
7
8
9
10
11

【题目2：删除记录 (二)】

--输入：
drop table if EXISTS exam_record;
CREATE TABLE IF NOT EXISTS exam_record (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
exam_id int NOT NULL COMMENT '试卷ID',
start_time datetime NOT NULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
TRUNCATE exam_record;
INSERT INTO exam_record(uid, exam_id, start_time, submit_time, score) VALUES
(1001, 9001, '2020-01-01 22:11:12', '2020-01-01 23:16:12', 50),
(1001, 9002, '2020-01-02 09:01:01', '2020-01-02 09:06:00', 58),
(1001, 9002, '2020-01-02 09:01:01', '2020-01-02 09:05:01', 58),
(1002, 9001, '2021-05-02 10:01:01', '2021-05-02 10:06:58', 60),
(1002, 9002, '2021-06-02 19:01:01', null, null),
(1003, 9001, '2021-09-05 19:01:01', null, null),
(1003, 9001, '2021-09-05 19:01:01', null, null),
(1003, 9002, '2021-09-09 07:01:02', null, null);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

delete from exam_record
where submit_time is null or timestampdiff(minute,start_time,submit_time)<5
order by start_time
limit 3
;
1
2
3
4
5

【题目3：删除记录 (三)】

--输入：
drop table if EXISTS exam_record;
CREATE TABLE IF NOT EXISTS exam_record (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
exam_id int NOT NULL COMMENT '试卷ID',
start_time datetime NOT NULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
TRUNCATE exam_record;
INSERT INTO exam_record(uid, exam_id, start_time, submit_time, score) VALUES
(1001, 9001, '2020-01-01 22:11:12', '2020-01-01 23:16:12', 50),
(1001, 9002, '2020-01-02 09:01:01', '2020-01-02 09:06:00', 58);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

truncate table exam_record;
1

二、表与索引操作

1. 表的创建、修改与删除

【题目1：创建一张新表】

--输入：
drop table if EXISTS user_info_vip;
1
2

表的创建、修改与删除：
1.1 直接创建表
CREATE TABLE [IF NOT EXISTS] tb_name – 不存在才创建，存在就跳过
(column_name1 data_type1 – 列名和类型必选
[ PRIMARY KEY – 可选的约束，主键
| FOREIGN KEY – 外键，引用其他表的键值
| AUTO_INCREMENT – 自增ID
| COMMENT comment – 列注释（评论）
| DEFAULT default_value – 默认值
| UNIQUE – 唯一性约束，不允许两条记录该列值相同
| NOT NULL – 该列非空
], …
) [CHARACTER SET charset] – 字符集编码
[COLLATE collate_value] – 列排序和比较时的规则（是否区分大小写等）
1.2 从另一张表复制表结构创建表：
CREATE TABLE tb_name LIKE tb_name_old
1.3 从另一张表的查询结果创建表：
CREATE TABLE tb_name AS SELECT * FROM tb_name_old WHERE options
2.1 修改表：ALTER TABLE 表名 修改选项
选项集合：
{ ADD COLUMN <列名> <类型> [first|after 字段名] – 增加列
| CHANGE COLUMN <旧列名> <新列名> <新列类型> – 修改列名
| MODIFY COLUMN <列名> <新类型> [新约束] – 修改列类型或约束
| MODIFY COLUMN <列名> <新类型> first – 将某一列放到第一列
| DROP COLUMN <列名> – 删除列
| RENAME TO <新表名> – 修改表名
| CHARACTER SET <字符集名> – 修改字符集
| COLLATE <校对规则名> } – 修改校对规则（比较和排序时用到）
3.1 删除表：DROP TABLE [IF EXISTS] 表名1 [ ,表名2]。

create table user_info_vip(
id     			INT(11)  		PRIMARY KEY AUTO_INCREMENT 		COMMENT '自增ID'
,uid   			INT(11)  		UNIQUE NOT NULL         		COMMENT '用户ID'
,nick_name   	VARCHAR(64)      								COMMENT '昵称'
,achievement	INT(11)     	DEFAULT 0 						COMMENT '成就值'
,level			INT(11)						   					COMMENT '用户等级'
,job			VARCHAR(32)					  					COMMENT '职业方向'
,register_time	DATETIME		DEFAULT CURRENT_TIMESTAMP		COMMENT '注册时间'
) CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT = '优质用户信息表'
;
1
2
3
4
5
6
7
8
9
10

【题目2：修改表】

--输入：
drop table if exists user_info;
CREATE TABLE IF NOT EXISTS user_info (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int UNIQUE NOT NULL COMMENT '用户ID',
`nick_name` varchar(64) COMMENT '昵称',
achievement int COMMENT '成就值',
level int COMMENT '用户等级',
job varchar(10) COMMENT '职业方向',
register_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
1
2
3
4
5
6
7
8
9
10
11

-- 易出错的地方:当想要设置default的值为数字0时,前面必须加上<列类型>为INT,否则会报default的错误
alter table user_info add column school varchar(15) after level;
alter table user_info change job profession varchar(10);
alter table user_info modify column achievement int(11) default 0;
1
2
3
4

【题目3：删除表】

--输入：
drop table if EXISTS exam_record;
CREATE TABLE IF NOT EXISTS exam_record (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
exam_id int NOT NULL COMMENT '试卷ID',
start_time datetime NOT NULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE TABLE IF NOT EXISTS exam_record_2010 (LIKE exam_record); 
CREATE TABLE IF NOT EXISTS exam_record_2012 (LIKE exam_record); 
CREATE TABLE IF NOT EXISTS exam_record_2013 (LIKE exam_record); 
CREATE TABLE IF NOT EXISTS exam_record_2014 (LIKE exam_record); 
CREATE TABLE IF NOT EXISTS exam_record_2015 (LIKE exam_record); 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

drop table if exists 
exam_record_2011, 
exam_record_2012, 
exam_record_2013, 
exam_record_2014;
1
2
3
4
5

2. 索引的创建、删除

【题目1：创建索引】

--输入：
drop table if exists examination_info;
CREATE TABLE IF NOT EXISTS examination_info (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
tag varchar(32) COMMENT '类别标签',
difficulty varchar(8) COMMENT '难度',
duration int NOT NULL COMMENT '时长',
release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_bin;
1
2
3
4
5
6
7
8
9
10

索引创建、删除与使用：
1.1 create方式创建索引：
CREATE
[UNIQUE – 唯一索引
| FULLTEXT – 全文索引
] INDEX index_name ON table_name – 不指定唯一或全文时默认普通索引
(column1[(length) [DESC|ASC]] [,column2,…]) – 可以对多列建立组合索引
1.2 alter方式创建索引：
ALTER TABLE tb_name ADD [UNIQUE | FULLTEXT] [INDEX] index_content(content) – 主键索引
2.1 drop方式删除索引：
DROP INDEX <索引名> ON <表名>
2.2 alter方式删除索引：
ALTER TABLE <表名> DROP INDEX <索引名>
3.1 索引的使用：
(1)索引使用时满足最左前缀匹配原则，即对于组合索引(col1, col2)，在不考虑引擎优化时，条件必须是col1在前col2在后，或者只使用col1，索引才会生效；
(2)索引不包含有NULL值的列
(3)一个查询只使用一次索引，where中如果使用了索引，order by就不会使用
(4)like做字段比较时只有前缀确定时才会使用索引
(5)在列上进行运算后不会使用索引，如year(start_time)<2020不会使用start_time上的索引
3.2 不同索引的区别：
(1)主键索引： 它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候指定了主键，就会创建主键索引， CREATE INDEX不能用来创建主键索引，使用 ALTER TABLE来代替。
(2)唯一索引：与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须一。
(3)普通索引：这是最基本的索引，它没有任何限制。
(4)全文索引：FULLTEXT索引用于全文搜索。只有InnoDB和 MyISAM存储引擎支持 FULLTEXT索引和仅适用于 CHAR， VARCHAR和 TEXT列。

CREATE INDEX 			idx_duration 		ON examination_info(duration);
CREATE UNIQUE INDEX 	uniq_idx_exam_id 	ON examination_info(exam_id);
CREATE FULLTEXT INDEX 	full_idx_tag 		ON examination_info(tag);
1
2
3

【题目2：删除索引】

--输入：
drop table if exists examination_info;
CREATE TABLE IF NOT EXISTS examination_info (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
exam_id int COMMENT '试卷ID',
tag varchar(32) COMMENT '类别标签',
difficulty varchar(8) COMMENT '难度',
duration int NOT NULL COMMENT '时长(分钟数)',
release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_bin;
CREATE INDEX idx_duration ON examination_info(duration);
CREATE UNIQUE INDEX uniq_idx_exam_id ON examination_info(exam_id);
ALTER TABLE examination_info ADD FULLTEXT full_idx_tag(tag);
1
2
3
4
5
6
7
8
9
10
11
12
13

-- 方法1：
DROP INDEX uniq_idx_exam_id ON examination_info;
DROP INDEX full_idx_tag ON examination_info;

-- 方法2：
ALTER TABLE examination_info DROP INDEX uniq_idx_exam_id;
ALTER TABLE examination_info DROP INDEX full_idx_tag;
1
2
3
4
5
6
7

三、聚合分组查询

1. 聚合函数

【题目1：SQL类别高难度试卷得分的截断平均值】

--输入：
drop table if exists examination_info;
CREATE TABLE  examination_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
    tag varchar(32) COMMENT '类别标签',
    difficulty varchar(8) COMMENT '难度',
    duration int NOT NULL COMMENT '时长',
    release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

drop table if exists exam_record;
CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'SQL', 'hard', 60, '2020-01-01 10:00:00'),
  (9002, '算法', 'medium', 80, '2020-08-02 10:00:00');

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:01', 80),
(1001, 9001, '2021-05-02 10:01:01', '2021-05-02 10:30:01', 81),
(1001, 9001, '2021-06-02 19:01:01', '2021-06-02 19:31:01', 84),
(1001, 9002, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89),
(1001, 9001, '2021-09-02 12:01:01', null, null),
(1001, 9002, '2021-09-01 12:01:01', null, null),
(1002, 9002, '2021-02-02 19:01:01', '2021-02-02 19:30:01', 87),
(1002, 9001, '2021-05-05 18:01:01', '2021-05-05 18:59:02', 90),
(1003, 9001, '2021-02-06 12:01:01', null, null),
(1003, 9001, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 50);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

select 
	b.tag
	,b.difficulty
	,round((sum(a.score)-max(a.score)-min(a.score))/(count(a.score)-2),1) clip_avg_score   # 截断平均值（去掉一个最大值和一个最小值后的平均值）
from (
		select 
			exam_id
			,score
		from exam_record
) a join (
			select 
				exam_id
				,tag
				,difficulty
			from examination_info
			where tag='SQL'					# SQL类别
			and difficulty='hard'			# 高难度
) b on a.exam_id=b.exam_id
group by b.tag,b.difficulty
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

【题目2：统计作答次数】

--输入：
drop table if exists exam_record;
CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:01', 80),
(1001, 9001, '2021-05-02 10:01:01', '2021-05-02 10:30:01', 81),
(1001, 9001, '2021-06-02 19:01:01', '2021-06-02 19:31:01', 84),
(1001, 9002, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89),
(1001, 9001, '2021-09-02 12:01:01', null, null),
(1001, 9002, '2021-09-01 12:01:01', null, null),
(1002, 9002, '2021-02-02 19:01:01', '2021-02-02 19:30:01', 87),
(1002, 9001, '2021-05-05 18:01:01', '2021-05-05 18:59:02', 90),
(1003, 9001, '2021-02-06 12:01:01', null, null),
(1003, 9001, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 89),
(1004, 9001, '2021-09-06 12:01:01', null, null);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

select 
	count(start_time) total_pv    													# 总作答次数
	,count(submit_time)	complete_pv													# 试卷已完成作答数
	,count(distinct if(submit_time is null,null,exam_id)) complete_exam_cnt			# 已完成的试卷数
from exam_record
;
;
1
2
3
4
5
6
7

【题目3：得分不小于平均分的最低分(这题有bug,运行不通过)】

--输入：
drop table if exists examination_info;
CREATE TABLE  examination_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
    tag varchar(32) COMMENT '类别标签',
    difficulty varchar(8) COMMENT '难度',
    duration int NOT NULL COMMENT '时长',
    release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

drop table if exists exam_record;
CREATE TABLE  exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'SQL', 'hard', 60, '2020-01-01 10:00:00'),
  (9002, 'SQL', 'easy', 60, '2020-02-01 10:00:00'),
  (9003, '算法', 'medium', 80, '2020-08-02 10:00:00');

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:01', 80),
(1002, 9001, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89),
(1002, 9002, '2021-09-02 12:01:01', null, null),
(1002, 9003, '2021-09-01 12:01:01', null, null),
(1002, 9001, '2021-02-02 19:01:01', '2021-02-02 19:30:01', 87),
(1002, 9002, '2021-05-05 18:01:01', '2021-05-05 18:59:02', 90),
(1003, 9002, '2021-02-06 12:01:01', null, null),
(1003, 9003, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 86),
(1004, 9003, '2021-09-06 12:01:01', null, null);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

select 
	min(score) min_score_over_avg			   # 用户最低得分
from (
		select 
			a.exam_id
			,a.score
			,avg(a.score) over() avg_score     # SQL试卷平均得分
		from (
				select 
					exam_id
					,score
				from exam_record
		) a join (
					select 
						exam_id
						,tag
					from examination_info
					where tag='SQL'				# SQL试卷
		) b on a.exam_id=b.exam_id
) a1
where score>=avg_score                       	# SQL试卷得分不小于该类试卷平均得分
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

2. 分组查询

【题目1：平均活跃天数和月活人数】

--输入：
drop table if exists exam_record;
CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2021-07-02 09:01:01', '2021-07-02 09:21:01', 80),
(1002, 9001, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 81),
(1002, 9002, '2021-09-02 12:01:01', null, null),
(1002, 9003, '2021-09-01 12:01:01', null, null),
(1002, 9001, '2021-07-02 19:01:01', '2021-07-02 19:30:01', 82),
(1002, 9002, '2021-07-05 18:01:01', '2021-07-05 18:59:02', 90),
(1003, 9002, '2021-07-06 12:01:01', null, null),
(1003, 9003, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 86),
(1004, 9003, '2021-09-06 12:01:01', null, null),
(1002, 9003, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 81),
(1005, 9001, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 88),
(1006, 9002, '2021-09-02 12:11:01', '2021-09-02 12:31:01', 89),
(1007, 9002, '2020-09-02 12:11:01', '2020-09-02 12:31:01', 89);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

select
	month
	,round(avg(active_days),2) avg_active_days                					# 平均活跃天数
	,count(uid)												  					# 月度活跃人数
from (
		select 
			date_format(submit_time,'%Y%m') month 
			,uid
			,count(distinct date(submit_time)) active_days    					# 每个月每个用户的活跃天数(解释中备注了：此处活跃指有交卷行为)
		from exam_record
		where substr(submit_time,1,4)='2021'and submit_time is not null			# 2021年活跃的用户作答记录
		group by date_format(submit_time,'%Y%m'),uid
) a 
group by month 
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

【题目2：月总刷题数和日均刷题数】

--输入：
drop table if exists practice_record;
CREATE TABLE  practice_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    question_id int NOT NULL COMMENT '题目ID',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO practice_record(uid,question_id,submit_time,score) VALUES
(1001, 8001, '2021-08-02 11:41:01', 60),
(1002, 8001, '2021-09-02 19:30:01', 50),
(1002, 8001, '2021-09-02 19:20:01', 70),
(1002, 8002, '2021-09-02 19:38:01', 70),
(1003, 8002, '2021-08-01 19:38:01', 80);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

-- 使用 WITH ROLLUP，此函数是对聚合函数进行求和，注意 with rollup是对 group by 后的第一个字段，进行分组计算。
select 
	ifnull(submit_month,'2021汇总') submit_month
	,month_q_cnt
	,round(avg_day_q_cnt,3)
from(
		select 
			date_format(submit_time,'%Y%m') submit_month
			,count(submit_time) month_q_cnt							# 月总刷题数
			,count(submit_time)/31 avg_day_q_cnt					# 日均刷题数，年度汇总平均无实际意义，这里我们按照31天来算5/31=0.161）
		from practice_record
		where substr(submit_time,1,4)='2021'
		group by date_format(submit_time,'%Y%m') 
		with rollup 
) a 
order by submit_month
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

【题目3：未完成试卷数大于1的有效用户】

--输入：
drop table if exists examination_info;
CREATE TABLE examination_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
    tag varchar(32) COMMENT '类别标签',
    difficulty varchar(8) COMMENT '难度',
    duration int NOT NULL COMMENT '时长',
    release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

drop table if exists exam_record;
CREATE TABLE  exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'SQL', 'hard', 60, '2020-01-01 10:00:00'),
  (9002, 'SQL', 'easy', 60, '2020-02-01 10:00:00'),
  (9003, '算法', 'medium', 80, '2020-08-02 10:00:00');

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2021-07-02 09:01:01', '2021-07-02 09:21:01', 80),
(1002, 9001, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 81),
(1002, 9002, '2021-09-02 12:01:01', null, null),
(1002, 9003, '2021-09-01 12:01:01', null, null),
(1002, 9001, '2021-07-02 19:01:01', '2021-07-02 19:30:01', 82),
(1002, 9002, '2021-07-05 18:01:01', '2021-07-05 18:59:02', 90),
(1003, 9002, '2021-07-06 12:01:01', null, null),
(1003, 9003, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 86),
(1004, 9003, '2021-09-06 12:01:01', null, null),
(1002, 9003, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 81),
(1005, 9001, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 88),
(1005, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 88),
(1006, 9002, '2021-09-02 12:11:01', '2021-09-02 12:31:01', 89);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

-- 对于每条作答tag，用:连接日期和tag：concat_ws(':', date(start_time), tag)
-- 对于一个人（组内）的多条作答，用;连接去重后的作答记录：group_concat(distinct concat_ws(':', date(start_time), tag) SEPARATOR ';')
select 
	a.uid
	,count(start_time)-count(submit_time) incomplete_cnt    								# 未完成试卷作答数
	,count(submit_time)		complete_cnt                     	 							# 完成试卷作答数
	,group_concat(distinct concat_ws(':',date(a.start_time),b.tag) separator ';') detail   	# 记得去重
from (
		select 
			uid
			,exam_id
			,start_time
			,submit_time
		from exam_record
) a left join (
				select 
					exam_id
					,tag
				from examination_info
) b on a.exam_id=b.exam_id
where year(start_time)='2021'             									# 2021年
group by a.uid
having count(submit_time)>=1 and (count(start_time)-count(submit_time))<5  	# 有效用户指完成试卷作答数至少为1且未完成数小于5
and (count(start_time)-count(submit_time))>1 								# 未完成试卷作答数大于1
order by incomplete_cnt desc
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

四、多表查询

1. 嵌套子查询

【题目1：月均完成试卷数不小于3的用户爱作答的类别】

--输入：
drop table if exists examination_info,exam_record;
CREATE TABLE examination_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
    tag varchar(32) COMMENT '类别标签',
    difficulty varchar(8) COMMENT '难度',
    duration int NOT NULL COMMENT '时长',
    release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'SQL', 'hard', 60, '2020-01-01 10:00:00'),
  (9002, 'C++', 'easy', 60, '2020-02-01 10:00:00'),
  (9003, '算法', 'medium', 80, '2020-08-02 10:00:00');

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2021-07-02 09:01:01', null, null),
(1002, 9003, '2021-09-01 12:01:01', '2021-09-01 12:21:01', 60),
(1002, 9002, '2021-09-02 12:01:01', '2021-09-02 12:31:01', 70),
(1002, 9001, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 81),
(1002, 9002, '2021-07-06 12:01:01', null, null),
(1003, 9003, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 86),
(1003, 9003, '2021-09-08 12:01:01', '2021-09-08 12:11:01', 40),
(1003, 9001, '2021-09-08 13:01:01', null, null),
(1003, 9002, '2021-09-08 14:01:01', null, null),
(1003, 9003, '2021-09-08 15:01:01', null, null),
(1005, 9001, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 88),
(1005, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 88),
(1005, 9002, '2021-09-02 12:11:01', '2021-09-02 12:31:01', 89);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

select 
	b.tag                    			# 类别
	,count(a.start_time) tag_cnt  		# 作答次数								
from (
		select 
			uid
			,exam_id
			,start_time
			,submit_time
		from exam_record
) a left join (
				select 
					exam_id
					,tag
				from examination_info
) b on a.exam_id=b.exam_id
where a.uid in (
					select 
						uid											# “当月均完成试卷数”不小于3的用户们
					from exam_record
					where submit_time is not null         			# 均完成的试卷
					group by uid,date_format(start_time,'%Y%m')
					having count(submit_time)>=3                    #  “当月均完成试卷数”不小于3
			   ) 
group by b.tag
order by tag_cnt desc
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

【题目2：试卷发布当天作答人数和平均分】

--输入：
drop table if exists examination_info,user_info,exam_record;
CREATE TABLE examination_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
    tag varchar(32) COMMENT '类别标签',
    difficulty varchar(8) COMMENT '难度',
    duration int NOT NULL COMMENT '时长',
    release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE user_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int UNIQUE NOT NULL COMMENT '用户ID',
    `nick_name` varchar(64) COMMENT '昵称',
    achievement int COMMENT '成就值',
    level int COMMENT '用户等级',
    job varchar(32) COMMENT '职业方向',
    register_time datetime COMMENT '注册时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO user_info(uid,`nick_name`,achievement,level,job,register_time) VALUES
  (1001, '牛客1号', 3100, 7, '算法', '2020-01-01 10:00:00'),
  (1002, '牛客2号', 2100, 6, '算法', '2020-01-01 10:00:00'),
  (1003, '牛客3号', 1500, 5, '算法', '2020-01-01 10:00:00'),
  (1004, '牛客4号', 1100, 4, '算法', '2020-01-01 10:00:00'),
  (1005, '牛客5号', 1600, 6, 'C++', '2020-01-01 10:00:00'),
  (1006, '牛客6号', 3000, 6, 'C++', '2020-01-01 10:00:00');

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'SQL', 'hard', 60, '2021-09-01 06:00:00'),
  (9002, 'C++', 'easy', 60, '2020-02-01 10:00:00'),
  (9003, '算法', 'medium', 80, '2020-08-02 10:00:00');

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2021-09-01 09:01:01', '2021-09-01 09:41:01', 70),
(1002, 9003, '2021-09-01 12:01:01', '2021-09-01 12:21:01', 60),
(1002, 9002, '2021-09-02 12:01:01', '2021-09-02 12:31:01', 70),
(1002, 9001, '2021-09-01 19:01:01', '2021-09-01 19:40:01', 80),
(1002, 9003, '2021-08-01 12:01:01', '2021-08-01 12:21:01', 60),
(1002, 9002, '2021-08-02 12:01:01', '2021-08-02 12:31:01', 70),
(1002, 9001, '2021-09-01 19:01:01', '2021-09-01 19:40:01', 85),
(1002, 9002, '2021-07-06 12:01:01', null, null),
(1003, 9003, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 86),
(1003, 9003, '2021-09-08 12:01:01', '2021-09-08 12:11:01', 40),
(1003, 9001, '2021-09-01 13:01:01', '2021-09-01 13:41:01', 70),
(1003, 9002, '2021-09-08 14:01:01', null, null),
(1003, 9003, '2021-09-08 15:01:01', null, null),
(1005, 9001, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 90),
(1005, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 88),
(1005, 9002, '2021-09-02 12:11:01', '2021-09-02 12:31:01', 89);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60

select 
	a.exam_id
	,count(distinct a.uid) 		uv   			# 人数
	,round(avg(a.score),1)		avg_score		# 平均分(计算平均分的基础是知道每张试卷的得分)
from (
		select 
			uid
			,exam_id
			,start_time
			,score
		from exam_record
) a join (
			select 
				uid
				,level 
			from user_info
			where level>'5'           			# 5级以上的用户
) b on a.uid=b.uid
join (
		select 
			exam_id
			,tag
			,release_time
		from examination_info
		where tag='SQL'             			# SQL类别试卷
) c on a.exam_id=c.exam_id
where date(a.start_time)=date(c.release_time) 	# 每张SQL类别试卷发布后，当天作答
group by a.exam_id
order by uv desc,avg_score asc
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

【题目3：作答试卷得分大于过80的人的用户等级分布】

--输入：
drop table if exists examination_info;
CREATE TABLE examination_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
    tag varchar(32) COMMENT '类别标签',
    difficulty varchar(8) COMMENT '难度',
    duration int NOT NULL COMMENT '时长',
    release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

drop table if exists user_info;
CREATE TABLE user_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int UNIQUE NOT NULL COMMENT '用户ID',
    `nick_name` varchar(64) COMMENT '昵称',
    achievement int COMMENT '成就值',
    level int COMMENT '用户等级',
    job varchar(32) COMMENT '职业方向',
    register_time datetime COMMENT '注册时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

drop table if exists exam_record;
CREATE TABLE  exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO user_info(uid,`nick_name`,achievement,level,job,register_time) VALUES
  (1001, '牛客1号', 3100, 7, '算法', '2020-01-01 10:00:00'),
  (1002, '牛客2号', 2100, 6, '算法', '2020-01-01 10:00:00'),
  (1003, '牛客3号', 1500, 5, '算法', '2020-01-01 10:00:00'),
  (1004, '牛客4号', 1100, 4, '算法', '2020-01-01 10:00:00'),
  (1005, '牛客5号', 1600, 6, 'C++', '2020-01-01 10:00:00'),
  (1006, '牛客6号', 3000, 6, 'C++', '2020-01-01 10:00:00');

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'SQL', 'hard', 60, '2021-09-01 06:00:00'),
  (9002, 'C++', 'easy', 60, '2021-09-01 06:00:00'),
  (9003, '算法', 'medium', 80, '2021-09-01 10:00:00');

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2021-09-01 09:01:01', '2021-09-01 09:41:01', 79),
(1002, 9003, '2021-09-01 12:01:01', '2021-09-01 12:21:01', 60),
(1002, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 70),
(1002, 9001, '2021-09-01 19:01:01', '2021-09-01 19:40:01', 80),
(1002, 9003, '2021-08-01 12:01:01', '2021-08-01 12:21:01', 60),
(1002, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 70),
(1002, 9001, '2021-09-01 19:01:01', '2021-09-01 19:40:01', 85),
(1002, 9002, '2021-09-01 12:01:01', null, null),
(1003, 9003, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 86),
(1003, 9003, '2021-09-08 12:01:01', '2021-09-08 12:11:01', 40),
(1003, 9001, '2021-09-01 13:01:01', '2021-09-01 13:41:01', 81),
(1003, 9002, '2021-09-01 14:01:01', null, null),
(1003, 9003, '2021-09-08 15:01:01', null, null),
(1005, 9001, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 90),
(1005, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 88),
(1006, 9002, '2021-09-01 12:11:01', '2021-09-01 12:31:01', 89);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

-- 这题有bug,需加上level倒序才能通过
select
	c.level
	,count(distinct a.uid) level_cnt		# 用户等级分布
from (
		select
			uid
			,exam_id
			,score
		from exam_record
		where score>80						# 得分大于80
) a join(
			select 
				exam_id
				,tag
			from examination_info
			where tag='SQL'					# SQL类别的试卷
) b on a.exam_id=b.exam_id
join (
		select 
			uid
			,level
		from user_info
) c on a.uid=c.uid
group by c.level
order by level_cnt desc,level desc          # 加上level倒序才能通过
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

2. 合并查询

【题目1：每个题目和每份试卷被作答的人数和次数】

--输入：
drop table if exists practice_record;
CREATE TABLE  practice_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    question_id int NOT NULL COMMENT '题目ID',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

drop table if exists exam_record;
CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO practice_record(uid,question_id,submit_time,score) VALUES
(1001, 8001, '2021-08-02 11:41:01', 60),
(1002, 8001, '2021-09-02 19:30:01', 50),
(1002, 8001, '2021-09-02 19:20:01', 70),
(1002, 8002, '2021-09-02 19:38:01', 70),
(1003, 8001, '2021-08-02 19:38:01', 70),
(1003, 8001, '2021-08-02 19:48:01', 90),
(1003, 8002, '2021-08-01 19:38:01', 80);

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2021-09-01 09:01:01', '2021-09-01 09:41:01', 81),
(1002, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 70),
(1002, 9001, '2021-09-01 19:01:01', '2021-09-01 19:40:01', 80),
(1002, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 70),
(1004, 9001, '2021-09-01 19:01:01', '2021-09-01 19:40:01', 85),
(1002, 9002, '2021-09-01 12:01:01', null, null);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

-- 先UNION后ORDER BY时，只需要在最后一个SELECT语句中包含ORDER BY；
select 
	exam_id tid
	,count(distinct uid) uv 
	,count(uid) pv
from exam_record 
group by exam_id
union all
select 
	question_id tid
	,count(distinct uid) uv 
	,count(uid) pv
from practice_record 
group by question_id
order by uv desc,pv desc
-- 先ORDER BY后UNION时，由于优先级问题，需要将子查询用括号括起来，且ORDER BY后面必须有 LIMIT`；
#-- 题目答案：
select 
	tid
	,uv
	,pv
from (
		select 
			exam_id tid
			,count(distinct uid) uv			# 每份试卷被作答的人数
			,count(uid) pv              	# 每份试卷被作答的次数
		from exam_record
		group by exam_id
		order by uv desc,pv desc
) a 
union all
select 
	tid
	,uv
	,pv
from (
		select 
			question_id tid
			,count(distinct uid) uv			# 每份题目被作答的人数
			,count(uid) pv                  # 每份题目被作答的次数
		from practice_record
		group by question_id
		order by uv desc,pv desc
) b 
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

【题目2：分别满足两个活动的人】

--输入：
drop table if exists examination_info;
CREATE TABLE examination_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
    tag varchar(32) COMMENT '类别标签',
    difficulty varchar(8) COMMENT '难度',
    duration int NOT NULL COMMENT '时长',
    release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;


drop table if exists exam_record;
CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'SQL', 'hard', 60, '2021-09-01 06:00:00'),
  (9002, 'C++', 'hard', 60, '2021-09-01 06:00:00'),
  (9003, '算法', 'medium', 80, '2021-09-01 10:00:00');

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2021-09-01 09:01:01', '2021-09-01 09:31:00', 81),
(1002, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 70),
(1003, 9001, '2021-09-01 19:01:01', '2021-09-01 19:40:01', 86),
(1003, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:51', 89),
(1004, 9001, '2021-09-01 19:01:01', '2021-09-01 19:30:01', 85);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

--方法1
select 
	uid 
	,'activity1' activity
from exam_record
where substr(start_time,1,4)='2021'
group by uid 
having min(score)>=85
union all 
select
	uid
	,'activity2' activity
from (
		select 
			uid
			,exam_id
			,start_time
			,submit_time
			,score 
		from exam_record 
		where substr(start_time,1,4)='2021'
		and score>80
) a  join (
				select 
					exam_id
					,difficulty
					,duration
				from examination_info
				where difficulty='hard'
) b on a.exam_id=b.exam_id
where timestampdiff(second,start_time,submit_time)<=duration*30
order by uid
;

-- 方法2
with t1 as(
			select 
				a.uid
				,a.exam_id
				,a.start_time
				,a.submit_time
				,a.score
				,b.duration
				,b.difficulty
				,b.tag
			from (
					select 
						uid
						,exam_id
						,start_time
						,submit_time
						,score
					from exam_record
			) a left join(
							select 
								exam_id
								,tag
								,difficulty
								,duration
							from examination_info
			) b on a.exam_id=b.exam_id
			where year(a.start_time)='2021'      # 2021年
)

select 
	uid
	,'activity1'  activity
from t1 
group by uid
having min(score)>=85  	 			# 每次试卷得分都能到85分
union all
select 
	uid
	,'activity2'  activity
from t1
where timestampdiff(second,start_time,submit_time)<=duration*30  # 至少有一次用了一半时间
-- where timestampdiff(minute,start_time,submit_time)<=duration*0.5  （记得用秒啊，题目的示例已经解释的很清楚了，不用秒会出错）
and difficulty='hard'   			# 高难度试卷
and score>80						# 分数大于80
group by uid 
order by uid
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82

比如，用户1003，如果用分钟来计算刚好是30分钟,满足活动2；但是如果用秒来计算就是1850秒，超过了1800秒，不能算满足活动2

3. 连接查询

【题目1：满足条件的用户的试卷完成数和题目练习数】

--输入：
drop table if exists examination_info,user_info,exam_record,practice_record;
CREATE TABLE examination_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
    tag varchar(32) COMMENT '类别标签',
    difficulty varchar(8) COMMENT '难度',
    duration int NOT NULL COMMENT '时长',
    release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE user_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int UNIQUE NOT NULL COMMENT '用户ID',
    `nick_name` varchar(64) COMMENT '昵称',
    achievement int COMMENT '成就值',
    level int COMMENT '用户等级',
    job varchar(32) COMMENT '职业方向',
    register_time datetime COMMENT '注册时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE practice_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    question_id int NOT NULL COMMENT '题目ID',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO user_info(uid,`nick_name`,achievement,level,job,register_time) VALUES
  (1001, '牛客1号', 3100, 7, '算法', '2020-01-01 10:00:00'),
  (1002, '牛客2号', 2300, 7, '算法', '2020-01-01 10:00:00'),
  (1003, '牛客3号', 2500, 7, '算法', '2020-01-01 10:00:00'),
  (1004, '牛客4号', 1200, 5, '算法', '2020-01-01 10:00:00'),
  (1005, '牛客5号', 1600, 6, 'C++', '2020-01-01 10:00:00'),
  (1006, '牛客6号', 2000, 6, 'C++', '2020-01-01 10:00:00');

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'SQL', 'hard', 60, '2021-09-01 06:00:00'),
  (9002, 'C++', 'hard', 60, '2021-09-01 06:00:00'),
  (9003, '算法', 'medium', 80, '2021-09-01 10:00:00');

INSERT INTO practice_record(uid,question_id,submit_time,score) VALUES
(1001, 8001, '2021-08-02 11:41:01', 60),
(1002, 8001, '2021-09-02 19:30:01', 50),
(1002, 8001, '2021-09-02 19:20:01', 70),
(1002, 8002, '2021-09-02 19:38:01', 70),
(1004, 8001, '2021-08-02 19:38:01', 70),
(1004, 8002, '2021-08-02 19:48:01', 90),
(1001, 8002, '2021-08-02 19:38:01', 70),
(1004, 8002, '2021-08-02 19:48:01', 90),
(1004, 8002, '2021-08-02 19:58:01', 94),
(1004, 8003, '2021-08-02 19:38:01', 70),
(1004, 8003, '2021-08-02 19:48:01', 90),
(1004, 8003, '2021-08-01 19:38:01', 80);

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2021-09-01 09:01:01', '2021-09-01 09:31:00', 81),
(1002, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 81),
(1003, 9001, '2021-09-01 19:01:01', '2021-09-01 19:40:01', 86),
(1003, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:51', 89),
(1004, 9001, '2021-09-01 19:01:01', '2021-09-01 19:30:01', 85),
(1005, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:02', 85),
(1006, 9003, '2021-09-07 10:01:01', '2021-09-07 10:21:01', 84),
(1006, 9001, '2021-09-07 10:01:01', '2021-09-07 10:21:01', 80);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74

-- 方法1
select 
	a.uid
	,ifnull(exam_cnt,0) exam_cnt
	,ifnull(question_cnt,0) question_cnt
from (
		-- 红名大佬2021年试卷总完成次数
		select 
			uid	
			,count(submit_time) exam_cnt	
		from exam_record 
		where substr(start_time,1,4)='2021'
		and uid in (
						-- 高难度SQL试卷得分平均值大于80并且是7级的红名大佬
						select 
							c.uid
						from (
								-- 高难度SQL试卷
								select 
									exam_id
								from examination_info
								where difficulty='hard'
								and tag='SQL' 
						) a join (
									select 
										uid
										,exam_id
										,score
									from exam_record
						) b on a.exam_id=b.exam_id
						join (
								-- 7级的红名大佬
								select 
									uid 
								from user_info
								where level=7
						) c on b.uid=c.uid 
						group by c.uid 
						having avg(score)>80
					) 
		group by uid
) a left join (
				-- 红名大佬2021年试卷总完成次数
				select 
					 uid
					 ,count(submit_time) question_cnt
				from practice_record
				where substr(submit_time,1,4)='2021'
				group by uid 
) b on a.uid=b.uid
order by exam_cnt asc,question_cnt desc
;

-- 方法2
with t1 as (
			select 
				a.uid                                      # 高难度SQL试卷得分平均值大于80并且是7级的红名大佬
			from (
					select 
						uid
						,exam_id
						,score
					from exam_record
			) a join (
						select 
							uid
							,level
						from user_info 
						where level='7'						# 7级的红名大佬
			) b on a.uid=b.uid 
			join (
					select 
						exam_id
						,tag
						,difficulty
					from examination_info
					where tag='SQL' and difficulty='hard'  	# 高难度SQL试卷
			) c on a.exam_id=c.exam_id
			group by a.uid
			having avg(a.score)>80          				# 平均值大于80
) 


select
	a.uid
	,a.exam_cnt 								
	,ifnull(b.question_cnt,0) question_cnt      
from(
		select 
			uid
			,count(submit_time) exam_cnt							 # 2021年试卷总完成次数
		from exam_record
		where year(submit_time)='2021' and submit_time is not null   # 只保留2021年有试卷完成记录的用户
		and uid in (select uid from t1)                              # 高难度SQL试卷得分平均值大于80并且是7级的红名大佬
		group by uid
) a left join (
				select 
					uid
					,count(submit_time) question_cnt   						 # 2021年题目总练习次数
				from practice_record
				where year(submit_time)='2021' and submit_time is not null   # 只保留2021年有试卷完成记录的用户
				group by uid
) b on a.uid=b.uid 
order by exam_cnt asc,question_cnt desc
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105

【题目2：每个6/7级用户活跃情况】

--输入：
drop table if exists examination_info,user_info,exam_record,practice_record;
CREATE TABLE examination_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
    tag varchar(32) COMMENT '类别标签',
    difficulty varchar(8) COMMENT '难度',
    duration int NOT NULL COMMENT '时长',
    release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE user_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int UNIQUE NOT NULL COMMENT '用户ID',
    `nick_name` varchar(64) COMMENT '昵称',
    achievement int COMMENT '成就值',
    level int COMMENT '用户等级',
    job varchar(32) COMMENT '职业方向',
    register_time datetime COMMENT '注册时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE practice_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    question_id int NOT NULL COMMENT '题目ID',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO user_info(uid,`nick_name`,achievement,level,job,register_time) VALUES
  (1001, '牛客1号', 3100, 7, '算法', '2020-01-01 10:00:00'),
  (1002, '牛客2号', 2300, 7, '算法', '2020-01-01 10:00:00'),
  (1003, '牛客3号', 2500, 7, '算法', '2020-01-01 10:00:00'),
  (1004, '牛客4号', 1200, 5, '算法', '2020-01-01 10:00:00'),
  (1005, '牛客5号', 1600, 6, 'C++', '2020-01-01 10:00:00'),
  (1006, '牛客6号', 2600, 7, 'C++', '2020-01-01 10:00:00');

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'SQL', 'hard', 60, '2021-09-01 06:00:00'),
  (9002, 'C++', 'easy', 60, '2021-09-01 06:00:00'),
  (9003, '算法', 'medium', 80, '2021-09-01 10:00:00');

INSERT INTO practice_record(uid,question_id,submit_time,score) VALUES
(1001, 8001, '2021-08-02 11:41:01', 60),
(1004, 8001, '2021-08-02 19:38:01', 70),
(1004, 8002, '2021-08-02 19:48:01', 90),
(1001, 8002, '2021-08-02 19:38:01', 70),
(1004, 8002, '2021-08-02 19:48:01', 90),
(1006, 8002, '2021-08-04 19:58:01', 94),
(1006, 8003, '2021-08-03 19:38:01', 70),
(1006, 8003, '2021-08-02 19:48:01', 90),
(1006, 8003, '2020-08-01 19:38:01', 80);

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2021-09-01 09:01:01', '2021-09-01 09:31:00', 78),
(1001, 9001, '2021-09-01 09:01:01', '2021-09-01 09:31:00', 81),
(1005, 9001, '2021-09-01 19:01:01', '2021-09-01 19:30:01', 85),
(1005, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:02', 85),
(1006, 9003, '2021-09-07 10:01:01', '2021-09-07 10:21:59', 84),
(1006, 9001, '2021-09-07 10:01:01', '2021-09-07 10:21:01', 81),
(1002, 9001, '2020-09-01 13:01:01', '2020-09-01 13:41:01', 81),
(1005, 9001, '2021-09-01 14:01:01', null, null);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71

with t1 as (
			select 
				uid
				,substr(submit_time,1,10) day_time
				,substr(submit_time,1,7)  month_time
				,'exam' type
			from exam_record
			union all
			select 
				uid
				,substr(submit_time,1,10) day_time
				,substr(submit_time,1,7)  month_time
				,'question' type
			from practice_record
) # 全连接 exam_record表和practice_record表，并重命名为t1

select 
	t2.uid
	,count(distinct month_time) act_month_total 															# 总活跃月份数
	,count(distinct if(year(day_time)='2021',day_time,null)) act_days_2021  								# 2021年活跃天数
	,count(distinct if(year(day_time)='2021' and type='exam',day_time,null)) act_days_2021_exam   			# 2021年试卷作答活跃天数
	,count(distinct if(year(day_time)='2021' and type='question',day_time,null)) act_days_2021_question  	# 2021年答题活跃天数
from t1
right join (
			select 
				uid
			from user_info
			where level in ('6','7')
) t2 on t1.uid=t2.uid   							# 因为要查看每一个6/7级用户的情况，故而右连user_info表
group by t2.uid
order by act_month_total desc,act_days_2021 desc 	# 按照总活跃月份数、2021年活跃天数降序排序
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

五、窗口函数

1. 专用窗口函数

【题目1：每类试卷得分前3名】

--输入：
drop table if exists examination_info,exam_record;
CREATE TABLE examination_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
    tag varchar(32) COMMENT '类别标签',
    difficulty varchar(8) COMMENT '难度',
    duration int NOT NULL COMMENT '时长',
    release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'SQL', 'hard', 60, '2021-09-01 06:00:00'),
  (9002, 'SQL', 'hard', 60, '2021-09-01 06:00:00'),
  (9003, '算法', 'medium', 80, '2021-09-01 10:00:00');

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2021-09-01 09:01:01', '2021-09-01 09:31:00', 78),
(1001, 9001, '2021-09-01 09:01:01', '2021-09-01 09:31:00', 81),
(1002, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 81),
(1003, 9001, '2021-09-01 19:01:01', '2021-09-01 19:40:01', 86),
(1003, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:51', 89),
(1004, 9001, '2021-09-01 19:01:01', '2021-09-01 19:30:01', 85),
(1005, 9003, '2021-09-01 12:01:01', '2021-09-01 12:31:02', 85),
(1006, 9003, '2021-09-07 10:01:01', '2021-09-07 10:21:01', 84),
(1003, 9003, '2021-09-08 12:01:01', '2021-09-08 12:11:01', 40),
(1003, 9002, '2021-09-01 14:01:01', null, null);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

select 
	tid
	,uid
	,ranking
from (
		select 
			a.tag tid
			,b.uid
			,max(b.score) max_score  	# 每类试卷下每个用户的最大分数
			,min(b.score) min_score	 	# 每类试卷下每个用户的最小分数
			,row_number() over(partition by a.tag order by max(b.score) desc,min(b.score) desc,b.uid desc) ranking  # 排名(如果两人最大分数相同，选择最小分数大者，如果还相同，选择uid大者)
		from (
						select 
							exam_id
							,tag 
						from examination_info
		) a left join (
										select 
											uid
											,exam_id
											,score
										from exam_record
		) b on a.exam_id=b.exam_id
		group by a.tag,b.uid
) a1
where ranking<=3   						# 每类试卷得分的前3名
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

【题目2：第二快/慢用时之差大于试卷时长一半的试卷】

--输入：
drop table if exists examination_info,exam_record;
CREATE TABLE examination_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
    tag varchar(32) COMMENT '类别标签',
    difficulty varchar(8) COMMENT '难度',
    duration int NOT NULL COMMENT '时长',
    release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'SQL', 'hard', 60, '2021-09-01 06:00:00'),
  (9002, 'C++', 'hard', 60, '2021-09-01 06:00:00'),
  (9003, '算法', 'medium', 80, '2021-09-01 10:00:00');

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2021-09-01 09:01:01', '2021-09-01 09:51:01', 78),
(1001, 9002, '2021-09-01 09:01:01', '2021-09-01 09:31:00', 81),
(1002, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 81),
(1003, 9001, '2021-09-01 19:01:01', '2021-09-01 19:59:01', 86),
(1003, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:51', 89),
(1004, 9002, '2021-09-01 19:01:01', '2021-09-01 19:30:01', 85),
(1005, 9001, '2021-09-01 12:01:01', '2021-09-01 12:31:02', 85),
(1006, 9001, '2021-09-07 10:01:01', '2021-09-07 10:12:01', 84),
(1003, 9001, '2021-09-08 12:01:01', '2021-09-08 12:11:01', 40),
(1003, 9002, '2021-09-01 14:01:01', null, null),
(1005, 9001, '2021-09-01 14:01:01', null, null),
(1003, 9003, '2021-09-08 15:01:01', null, null);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

select 
	exam_id
	,duration 
	,release_time
from (
		select 
			a.exam_id
			,a.diff
			,nth_value(a.diff,2) over(partition by a.exam_id order by diff desc rows between unbounded preceding and unbounded following)    low_2    # 第二慢
			,nth_value(a.diff,2) over(partition by a.exam_id order by diff rows between unbounded preceding and unbounded following)   		 fast_2   # 第二快
			,b.duration
			,b.release_time
		from (
				select
					exam_id
					,start_time
					,submit_time
					,timestampdiff(minute,start_time,submit_time) diff
				from exam_record
		) a left join (
						select 
							exam_id
							,duration 
							,release_time
						from examination_info
		) b on a.exam_id=b.exam_id
) a1
where (low_2-fast_2)>(duration*0.5)   # 第二快和第二慢用时之差大于试卷时长的一半
group by exam_id,duration ,release_time
order by exam_id desc
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

【题目3：连续两次作答试卷的最大时间窗】

--输入：
drop table if exists exam_record;
CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1006, 9003, '2021-09-07 10:01:01', '2021-09-07 10:21:02', 84),
(1006, 9001, '2021-09-01 12:11:01', '2021-09-01 12:31:01', 89),
(1006, 9002, '2021-09-06 10:01:01', '2021-09-06 10:21:01', 81),
(1005, 9002, '2021-09-05 10:01:01', '2021-09-05 10:21:01', 81),
(1005, 9001, '2021-09-05 10:31:01', '2021-09-05 10:51:01', 81);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

select 
	uid
	,days_window
	,round(exam_cnt/max_min_day*days_window,2) avg_exam_cnt 
from (
		select 
			uid
			,count(start_time) exam_cnt   				         					# 每个用户总作答次数
-- 			,timestampdiff(day,min(start_time),max(start_time))+1  max_min_day 		
-- 			,max(timestampdiff(day,last_start_time,start_time)+1)  days_window		
			,datediff(max(start_time),min(start_time))+1 max_min_day				# 最早一次作答和最晚一次作答的相差天数 
			,max(datediff(start_time,last_start_time)+1) days_window				# 连续两次作答试卷的最大时间窗
		from (
				select 
					uid
					,exam_id
					,lag(start_time,1) over(partition by uid order by start_time) last_start_time  # 前一次作答时间 
					,start_time 																														# 下一次作答时间
				from exam_record
				where year(start_time)='2021'  			# 2021年
		) a 
		group by uid
) a1
where max_min_day>1  									# 最早一次作答和最晚一次作答时间窗>1的用户
order by days_window desc,avg_exam_cnt desc
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

为什么用max(timestampdiff(day,last_start_time,start_time)+1) days_window 求最大时间窗是错的？

datediff()函数的作用是求日期差，也就是把一个时间的日期部分取出来求差。例如：'2021-09-05 12:00:00’和’2021-09-04 11:00:00’这两个日期，datediff只取2021-09-05和2021-09-04求日期差，并不会管后面的时间部分。
timestampdiff()函数的作用则是求时间戳的差，例如：'2021-09-05 12:00:00’和’2021-09-04 11:00:00’这两个日期，datediff只会先求出这个日期的时分秒差，之后再转换成天数来求日期差。

直接说可能有点懵，看完差别后，具体来看下面这个例子：

#先看第一组时间差是23小时，日期差（9月5日-9月4日）是1天
select 
	DATEDIFF('2021-09-05 12:00:00','2021-09-04 11:00:00') datediff_1
	,TIMESTAMPDIFF(hour,'2021-09-04 12:00:00','2021-09-05 11:00:00') hourdiff_1
	,TIMESTAMPDIFF(day,'2021-09-04 12:00:00','2021-09-05 11:00:00')timediff_1 
1
2
3
4
5

#再看第二组时间差是28小时，日期差（9月5日-9月4日）是1天
select 
	DATEDIFF('2021-09-05 16:00:00','2021-09-04 11:00:00') datediff_2
	,TIMESTAMPDIFF(hour,'2021-09-04 12:00:00','2021-09-05 16:00:00') hourdiff_2
	,TIMESTAMPDIFF(day,'2021-09-04 12:00:00','2021-09-05 16:00:00')timediff_2
1
2
3
4
5

具体到这个题目中，用timestampdiff计算得出的最大时间窗是5天，用datediff计算得出的最大时间窗户是6天

【题目4：近三个月未完成试卷数为0的用户完成情况】

--输入：
drop table if exists exam_record;
CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1006, 9003, '2021-09-06 10:01:01', '2021-09-06 10:21:02', 84),
(1006, 9001, '2021-08-02 12:11:01', '2021-08-02 12:31:01', 89),
(1006, 9002, '2021-06-06 10:01:01', '2021-06-06 10:21:01', 81),
(1006, 9002, '2021-05-06 10:01:01', '2021-05-06 10:21:01', 81),
(1006, 9001, '2021-05-01 12:01:01', null, null),
(1001, 9001, '2021-09-05 10:31:01', '2021-09-05 10:51:01', 81),
(1001, 9003, '2021-08-01 09:01:01', '2021-08-01 09:51:11', 78),
(1001, 9002, '2021-07-01 09:01:01', '2021-07-01 09:31:00', 81),
(1001, 9002, '2021-07-01 12:01:01', '2021-07-01 12:31:01', 81),
(1001, 9002, '2021-07-01 12:01:01', null, null);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

select 
	uid 
	,count(submit_time) exam_complete_cnt   	# 用户的试卷作答完成数
from (
		select 
			uid
			,exam_id
			,start_time 
			,submit_time
			,dense_rank() over(partition by uid order by date_format(start_time,'%Y%m') desc) rn   # 对月份进行排序，需要对时间格式进行处理 date_format(start_time,'%Y-%m')
		from exam_record
) a 
where rn<=3 									# 每个人近三个有试卷作答记录的月份: 对每个用户的试卷作答记录的时间按照月份进行排序，取离当前最近的前3个月
group by uid 
having count(start_time)=count(submit_time)  	# 没有试卷是未完成状态的:剔除在3个月中有未完成状态试卷的用户，即作答数=完成数
order by exam_complete_cnt desc,uid desc
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

这里为什么要用dense_rank？
因为对每个用户的试卷作答记录的时间按照月份进行排序，即取离当前最近的前3个月。
对相同的数，给相同的排名，且不跳过，例如 1 1 2 3 3 3 4 这样的排名方式，故而选择dense_rank() over() 函数进行排序
dense_rank()over(partition by uid order by start_time desc) AS rk 当rk<=3时满足题设需求。

select 
	uid
	,start_time
	,submit_time
	,dense_rank()over(partition by uid order by date_format(start_time,'%y-%m') desc) as rk
from exam_record
1
2
3
4
5
6

【题目5：未完成率较高的50%用户近二个月答卷情况】

--输入：
drop table if exists examination_info,user_info,exam_record;
CREATE TABLE examination_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
    tag varchar(32) COMMENT '类别标签',
    difficulty varchar(8) COMMENT '难度',
    duration int NOT NULL COMMENT '时长',
    release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE user_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int UNIQUE NOT NULL COMMENT '用户ID',
    `nick_name` varchar(64) COMMENT '昵称',
    achievement int COMMENT '成就值',
    level int COMMENT '用户等级',
    job varchar(32) COMMENT '职业方向',
    register_time datetime COMMENT '注册时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO user_info(uid,`nick_name`,achievement,`level`,job,register_time) VALUES
  (1001, '牛客1', 3200, 7, '算法', '2020-01-01 10:00:00'),
  (1002, '牛客2号', 2500, 6, '算法', '2020-01-01 10:00:00'),
  (1003, '牛客3号♂', 2200, 5, '算法', '2020-01-01 10:00:00');

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'SQL', 'hard', 60, '2020-01-01 10:00:00'),
  (9002, 'SQL', 'hard', 80, '2020-01-01 10:00:00'),
  (9003, '算法', 'hard', 80, '2020-01-01 10:00:00'),
  (9004, 'PYTHON', 'medium', 70, '2020-01-01 10:00:00');
	
INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-01 09:01:01', '2020-01-01 09:21:59', 90),
(1002, 9001, '2020-01-20 10:01:01', null, null),
(1002, 9001, '2020-02-01 12:11:01', null, null),
(1003, 9001, '2020-03-01 19:01:01', null, null),
(1001, 9001, '2020-03-01 12:01:01', null, null),
(1002, 9001, '2020-03-01 12:01:01', '2020-03-01 12:41:01', 90),
(1002, 9001, '2020-05-02 19:01:01', '2020-05-02 19:32:00', 90),
(1001, 9002, '2020-01-02 19:01:01', '2020-01-02 19:59:01', 69),
(1001, 9002, '2020-02-02 12:01:01', '2020-02-02 12:20:01', 99),
(1002, 9002, '2020-02-02 12:01:01', null, null),
(1002, 9002, '2020-02-02 12:01:01', '2020-02-02 12:43:01', 81),
(1002, 9002, '2020-03-02 12:11:01', null, null),
(1001, 9001, '2020-01-02 10:01:01', '2020-01-02 10:31:01', 89),
(1001, 9002, '2020-01-01 12:11:01', null, null),
(1002, 9001, '2020-01-01 18:01:01', '2020-01-01 18:59:02', 90),
(1002, 9003, '2020-05-06 12:01:01', null, null),
(1001, 9002, '2020-05-05 18:01:01', null, null);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

-- 题目解释中说“1001、1002、1003分别排在1.0、0.5、0.0的位置”，有意让我们用percent_rank，实际cume_dist更常用
with t1 as (
			select 
				uid
			from (
					select 
						uid
						,(count(start_time)-count(submit_time))/count(start_time)  incomplete_rate     # 未完成率
						,percent_rank() over(order by (count(start_time)-count(submit_time))/count(start_time) desc)   incomplete_percent        # 题目说了是从0开始的，所以用percent_rank，cume_dist是从1开始的
					from exam_record
					where exam_id in (
										select 
											exam_id
										from examination_info
										where tag='SQL'          # SQL试卷								
									 ) 
					group by uid
			) a 
			where incomplete_percent<=0.5     					# 未完成率较高的50%用户，因为按未完成率倒序了，所以<=
			and uid in (
						 select 
							uid
						 from user_info
						 where level in ('6','7')     			# 6级和7级用户
					   )
) 

select 
	uid
	,date_format(start_time,'%Y%m') start_month
	,count(start_time) total_cnt				# 每个月的答卷数目
	,count(submit_time) complete_cnt			# 每个月的完成数目	
from (
		select 
			uid
			,start_time
			,submit_time
			,dense_rank() over(partition by uid order by date_format(start_time,'%Y%m') desc) dk 
		from exam_record
		where uid in (select uid from t1)
) a 
where dk<=3										# 在有试卷作答记录的近三个月中
group by uid,date_format(start_time,'%Y%m')
order by  uid,start_month
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

【题目6：试卷完成数同比2020年的增长率及排名变化】

--输入：
drop table if exists examination_info,exam_record;
CREATE TABLE examination_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
    tag varchar(32) COMMENT '类别标签',
    difficulty varchar(8) COMMENT '难度',
    duration int NOT NULL COMMENT '时长',
    release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'SQL', 'hard', 60, '2020-01-01 10:00:00'),
  (9002, 'C++', 'hard', 80, '2020-01-01 10:00:00'),
  (9003, '算法', 'hard', 80, '2020-01-01 10:00:00'),
  (9004, 'PYTHON', 'medium', 70, '2020-01-01 10:00:00');
	
INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-08-02 10:01:01', '2020-08-02 10:31:01', 89),
(1002, 9001, '2020-04-01 18:01:01', '2020-04-01 18:59:02', 90),
(1001, 9001, '2020-04-01 09:01:01', '2020-04-01 09:21:59', 80),
(1002, 9003, '2021-01-20 10:01:01', '2021-01-20 10:10:01', 81),
(1002, 9001, '2021-03-02 19:01:01', '2021-03-02 19:32:00', 20),
(1001, 9003, '2021-04-02 19:01:01', '2021-04-02 19:40:01', 89),
(1004, 9004, '2020-05-02 12:01:01', '2020-05-02 12:20:01', 99),
(1003, 9001, '2021-05-02 12:01:01', '2021-05-02 12:31:01', 98),
(1001, 9002, '2020-02-02 12:01:01', '2020-02-02 12:20:01', 99),
(1002, 9002, '2020-02-02 12:01:01', '2020-02-02 12:43:01', 81),
(1001, 9002, '2020-01-02 19:01:01', '2020-01-02 19:59:01', 69),
(1001, 9004, '2021-09-02 12:11:01', null, null),
(1003, 9001, '2020-01-02 10:01:01', '2020-01-02 10:31:01', 89),
(1002, 9004, '2020-01-01 12:11:01', '2020-01-01 12:31:01', 83),
(1002, 9003, '2021-01-01 18:01:01', '2021-01-01 18:59:02', 90),
(1002, 9002, '2020-02-02 12:01:01', null, null),
(1002, 9002, '2020-03-02 12:11:01', null, null),
(1001, 9002, '2021-05-05 18:01:01', null, null);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

with t1 as(
			select 
				a.exam_id
				,b.tag
				,a.start_time
				,a.submit_time	
			from (
					select
						exam_id
						,start_time
						,submit_time
					from exam_record
			) a left join (
							select 
								exam_id
								,tag
							from examination_info
			) b on a.exam_id=b.exam_id
) 

select 
	a.tag
	,a.exam_cnt_20           
	,b.exam_cnt_21
	,concat(round((b.exam_cnt_21/a.exam_cnt_20-1)*100,1),'%')  growth_rate				# 2021年上半年各类试卷的做完次数相比2020年上半年同期的增长率（百分比格式，保留1位小数）
	,a.exam_cnt_rank_20
	,b.exam_cnt_rank_21	
	,cast(b.exam_cnt_rank_21 as signed)-cast(a.exam_cnt_rank_20 as signed) rank_delta  	# 2021年上半年相较于2020年上半年各类试卷做完次数排名变化（需要将value转换成INT(有符号的整数)格式，否则会报错）
from (
		select 
			tag
			,count(submit_time) exam_cnt_20         									# 2020年上半年各类试卷的做完次数
			,rank() over(order by count(submit_time) desc) exam_cnt_rank_20   			# 2020年上半年各类试卷做完次数排名
		from t1		
		where substr(start_time,1,10) between '2020-01-01' and '2020-06-30'    			# 2020年上半年
		group by tag
		having count(submit_time)>0
) a left join (
				select 
					tag
					,count(submit_time) exam_cnt_21   									# 2021年上半年各类试卷的做完次数
					,rank() over(order by count(submit_time) desc) exam_cnt_rank_21   	# 2021年上半年各类试卷做完次数排名
				from t1
				where substr(start_time,1,10) between '2021-01-01' and '2021-06-30'    	# 2021年上半年
				group by tag
				having count(submit_time)>0
) b on a.tag=b.tag
where b.exam_cnt_21 is not null 
order by growth_rate desc,b.exam_cnt_rank_21 desc 								        # 按增长率和21年排名降序输出
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

这里需要注意：
exam_cnt_rank_21 - exam_cnt_rank_20
⚠️⚠️⚠️直接相减系统报错，需要对exam_cnt_rank_21 和 exam_cnt_rank_20的格式进行强行更改为整数。
CAST(exam_cnt_rank_21 AS SIGNED ) - CAST(exam_cnt_rank_20 AS SIGNED ) AS rank_delta

2. 聚合窗口函数

【题目1：对试卷得分做min-max归一化】

--输入：
drop table if exists examination_info,exam_record;
CREATE TABLE examination_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
    tag varchar(32) COMMENT '类别标签',
    difficulty varchar(8) COMMENT '难度',
    duration int NOT NULL COMMENT '时长',
    release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_bin;

CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'SQL', 'hard', 60, '2020-01-01 10:00:00'),
  (9002, 'C++', 'hard', 80, '2020-01-01 10:00:00'),
  (9003, '算法', 'hard', 80, '2020-01-01 10:00:00'),
  (9004, 'PYTHON', 'medium', 70, '2020-01-01 10:00:00'),
  (9005, 'WEB', 'hard', 80, '2020-01-01 10:00:00'),
  (9006, 'PYTHON', 'hard', 80, '2020-01-01 10:00:00'),
  (9007, 'web', 'hard', 80, '2020-01-01 10:00:00'),
  (9008, 'Web', 'medium', 70, '2020-01-01 10:00:00'),
  (9009, 'WEB', 'medium', 70, '2020-01-01 10:00:00'),
  (9010, 'SQL', 'medium', 70, '2020-01-01 10:00:00');
	
INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-01 09:01:01', '2020-01-01 09:21:59', 90),
(1003, 9002, '2020-01-01 19:01:01', '2020-01-01 19:30:01', 75),
(1004, 9002, '2020-01-01 12:01:01', '2020-01-01 12:11:01', 60),
(1003, 9002, '2020-01-01 12:01:01', '2020-01-01 12:41:01', 90),
(1002, 9002, '2020-01-02 19:01:01', '2020-01-02 19:32:00', 90),
(1003, 9001, '2020-01-02 12:01:01', '2020-01-02 12:31:01', 68),
(1001, 9002, '2020-01-02 12:01:01', '2020-01-02 12:43:01', 81),
(1001, 9005, '2020-01-02 12:11:01', null, null),
(1001, 9001, '2020-01-02 10:01:01', '2020-01-02 10:31:01', 89),
(1002, 9002, '2020-01-01 12:11:01', '2020-01-01 12:31:01', 83),
(1002, 9004, '2021-09-06 12:01:01', null, null),
(1002, 9002, '2021-05-05 18:01:01', null, null);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

select 
	uid	
	,exam_id
	,round(avg(min_max_score),0) avg_new_score
from (
		select
			exam_id
			,uid
			,if(min_score=max_score,score,(score-min_score)/(max_score-min_score)*100)    min_max_score     # min-max归一化后缩放到[0,100]区间（如果某个试卷作答记录中只有一个得分，那么无需使用公式，归一化并缩放后分数仍为原分数）
		from (
				select 
					exam_id
					,uid
					,score
					,min(score) over(partition by exam_id) min_score   	 # 每类试卷的最小分数
					,max(score) over(partition by exam_id) max_score	 # 每类试卷的最大分数
				from exam_record
				where exam_id in (
									select 
										exam_id
									from examination_info
									where difficulty='hard'   			 # 高难度试卷
								 ) 
				and score is not null 									 # 得分不为null
		) a 
) a1
group by exam_id,uid
order by exam_id asc,avg_new_score desc 								 # 按照试卷ID升序，评价分降序排序
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

主要考察聚类窗口函数，和聚类窗口函数的用法和GROUP BY 函数类似。

• MIN()OVER() :不改变表结构的前提下，计算出最小值
• MAX()OVER():不改变表结构的前提下，计算出最大值
• COUNT()OVER():不改变表结构的前提下，计数
• SUM()OVER():不改变表结构的前提下，求和
• AVG()OVER():不改变表结构的前提下，求平均值
窗口函数的关键词是“不改变表格结构”，查出的数据，单独放一列。
  
  
  

【题目2：每份试卷每月作答数和截止当月的作答总数】

--输入：
drop table if exists exam_record;
CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-01 09:01:01', '2020-01-01 09:21:59', 90),
(1002, 9001, '2020-01-20 10:01:01', '2020-01-20 10:10:01', 89),
(1002, 9001, '2020-02-01 12:11:01', '2020-02-01 12:31:01', 83),
(1003, 9001, '2020-03-01 19:01:01', '2020-03-01 19:30:01', 75),
(1004, 9001, '2020-03-01 12:01:01', '2020-03-01 12:11:01', 60),
(1003, 9001, '2020-03-01 12:01:01', '2020-03-01 12:41:01', 90),
(1002, 9001, '2020-05-02 19:01:01', '2020-05-02 19:32:00', 90),
(1001, 9002, '2020-01-02 19:01:01', '2020-01-02 19:59:01', 69),
(1004, 9002, '2020-02-02 12:01:01', '2020-02-02 12:20:01', 99),
(1003, 9002, '2020-02-02 12:01:01', '2020-02-02 12:31:01', 68),
(1001, 9002, '2020-02-02 12:01:01', '2020-02-02 12:43:01', 81),
(1001, 9002, '2020-03-02 12:11:01', null, null);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

select 
	exam_id
	,date_format(start_time,'%Y%m') start_month 
	,count(start_time) month_cnt                  #每份试卷每月作答数
	,sum(count(start_time)) over(partition by exam_id order by date_format(start_time,'%Y%m')) cum_exam_cnt   #每份试卷截止当月的作答总数
from exam_record
group by exam_id,date_format(start_time,'%Y%m')
;
1
2
3
4
5
6
7
8

【题目3：每月及截止当月的答题情况】

--输入：
drop table if exists exam_record;
CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-01 09:01:01', '2020-01-01 09:21:59', 90),
(1002, 9001, '2020-01-20 10:01:01', '2020-01-20 10:10:01', 89),
(1002, 9001, '2020-02-01 12:11:01', '2020-02-01 12:31:01', 83),
(1003, 9001, '2020-03-01 19:01:01', '2020-03-01 19:30:01', 75),
(1004, 9001, '2020-03-01 12:01:01', '2020-03-01 12:11:01', 60),
(1003, 9001, '2020-03-01 12:01:01', '2020-03-01 12:41:01', 90),
(1002, 9001, '2020-05-02 19:01:01', '2020-05-02 19:32:00', 90),
(1001, 9002, '2020-01-02 19:01:01', '2020-01-02 19:59:01', 69),
(1004, 9002, '2020-02-02 12:01:01', '2020-02-02 12:20:01', 99),
(1003, 9002, '2020-02-02 12:01:01', '2020-02-02 12:31:01', 68),
(1001, 9002, '2020-02-02 12:01:01', '2020-02-02 12:43:01', 81),
(1001, 9002, '2020-03-02 12:11:01', null, null);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

select 
	date_format(start_time,'%Y%m') start_month
	,count(distinct uid) mau 																										# 月活用户数											
	,count(distinct if(first_start_time is not null,uid,null)) 	month_add_uv   														# 新增用户数
	,max(count(distinct if(first_start_time is not null,uid,null))) over(order by date_format(start_time,'%Y%m')) max_month_add_uv  # 截止当月的单月最大新增用户数
	,sum(count(distinct if(first_start_time is not null,uid,null))) over(order by date_format(start_time,'%Y%m')) cum_sum_uv   		# 截止当月的累积用户数
from (
		select 
			uid
			,exam_id
			,start_time
			,if(start_time=first_start_time,first_start_time,null)  first_start_time    # 若用户的登录时间等于首次登录时间则定义为新用户
		from (
				select 
					uid
					,exam_id
					,start_time 
					,min(start_time) over(partition by uid) first_start_time   			# 用户首次作答时间
				from exam_record
		) a  
) a1
group by date_format(start_time,'%Y%m') 
order by start_month
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

六、其他常用操作

1. 空值处理

【题目1：统计有未完成状态的试卷的未完成数和未完成率0级用户高难度试卷的平均用时和平均得分】

--输入；
drop table if exists exam_record;
CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:01', 80),
(1001, 9001, '2021-05-02 10:01:01', '2021-05-02 10:30:01', 81),
(1001, 9001, '2021-09-02 12:01:01', null, null);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

-- 方法1
select 
	exam_id
	,sum(if(submit_time is not null,0,1)) incomplete_cnt   									# 未完成数
	, round(sum(if(submit_time is not null,0,1))/count(start_time),3)  incomplete_rate 		# 未完成率
from exam_record
group by exam_id
having sum(if(submit_time is not null,0,1))>=1
;

-- 方法2
select 
	exam_id
	,count(start_time)-count(submit_time)  incomplete_cnt									# 未完成数（总数-完成数）
	,round((count(start_time)-count(submit_time))/(count(start_time)),3) incomplete_rate 	# 未完成率（（总数-完成数）/总数）
from exam_record
group by exam_id
having count(start_time)-count(submit_time)>0    											# 有未完成状态的试卷（未完成数大于0）
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

【题目2：0级用户高难度试卷的平均用时和平均得分】

--输入：
drop table if exists examination_info,user_info,exam_record;
CREATE TABLE examination_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
    tag varchar(32) COMMENT '类别标签',
    difficulty varchar(8) COMMENT '难度',
    duration int NOT NULL COMMENT '时长',
    release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE user_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int UNIQUE NOT NULL COMMENT '用户ID',
    `nick_name` varchar(64) COMMENT '昵称',
    achievement int COMMENT '成就值',
    level int COMMENT '用户等级',
    job varchar(32) COMMENT '职业方向',
    register_time datetime COMMENT '注册时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO user_info(uid,`nick_name`,achievement,level,job,register_time) VALUES
  (1001, '牛客1号', 10, 0, '算法', '2020-01-01 10:00:00'),
  (1002, '牛客2号', 2100, 6, '算法', '2020-01-01 10:00:00');

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'SQL', 'hard', 60, '2020-01-01 10:00:00'),
  (9002, 'SQL', 'easy', 60, '2020-01-01 10:00:00'),
  (9004, '算法', 'medium', 80, '2020-01-01 10:00:00');
	
INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:59', 80),
(1001, 9001, '2021-05-02 10:01:01', null, null),
(1001, 9002, '2021-02-02 19:01:01', '2021-02-02 19:30:01', 87),
(1001, 9001, '2021-06-02 19:01:01', '2021-06-02 19:32:00', 20),
(1001, 9002, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89),
(1001, 9002, '2021-09-01 12:01:01', null, null),
(1002, 9002, '2021-05-05 18:01:01', '2021-05-05 18:59:02', 90);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

select 
	uid
	,nick_name
	,achievement
from user_info
where achievement between '1200' and '2500'   	# 成就值在1200~2500之间
and nick_name like '牛客%号'                   	# 昵称以『牛客』开头『号』结尾
and uid in (
				select 
					uid
				from exam_record
				group by uid
				having date_format(max(start_time),'%Y%m')='202109'  	# 最近一次活跃作答试卷在2021年9月
				union                                                	# 答题或作答试卷，用union去重
				select 
					uid
				from practice_record
				group by uid
				having date_format(max(submit_time),'%Y%m')='202109'  	# 最近一次活跃答题在2021年9月
		   ) 
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

2. 高级条件语句

【题目1：筛选限定昵称成就值活跃日期的用户】

--输入：
drop table if exists user_info,exam_record,practice_record;
CREATE TABLE user_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int UNIQUE NOT NULL COMMENT '用户ID',
    `nick_name` varchar(64) COMMENT '昵称',
    achievement int COMMENT '成就值',
    level int COMMENT '用户等级',
    job varchar(32) COMMENT '职业方向',
    register_time datetime COMMENT '注册时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE practice_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    question_id int NOT NULL COMMENT '题目ID',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO user_info(uid,`nick_name`,achievement,level,job,register_time) VALUES
  (1001, '牛客1号', 1000, 2, '算法', '2020-01-01 10:00:00'),
  (1002, '牛客2号', 1200, 3, '算法', '2020-01-01 10:00:00'),
  (1003, '进击的3号', 2200, 5, '算法', '2020-01-01 10:00:00'),
  (1004, '牛客4号', 2500, 6, '算法', '2020-01-01 10:00:00'),
  (1005, '牛客5号', 3000, 7, 'C++', '2020-01-01 10:00:00');

INSERT INTO practice_record(uid,question_id,submit_time,score) VALUES
(1001, 8001, '2021-08-02 11:41:01', 60),
(1002, 8001, '2021-09-02 19:30:01', 50),
(1002, 8001, '2021-09-02 19:20:01', 70),
(1002, 8002, '2021-09-02 19:38:01', 70),
(1003, 8002, '2021-09-01 19:38:01', 80);

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:59', 80),
(1001, 9001, '2021-05-02 10:01:01', null, null),
(1001, 9002, '2021-02-02 19:01:01', '2021-02-02 19:30:01', 87),
(1001, 9001, '2021-06-02 19:01:01', '2021-06-02 19:32:00', 20),
(1001, 9002, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89),
(1001, 9002, '2021-09-01 12:01:01', null, null),
(1002, 9002, '2021-05-05 18:01:01', '2021-05-05 18:59:02', 90),
(1003, 9003, '2021-02-06 12:01:01', null, null),
(1003, 9001, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 89),
(1004, 9002, '2021-08-06 12:01:01', null, null),
(1002, 9001, '2020-01-01 12:01:01', '2020-01-01 12:31:01', 81),
(1002, 9002, '2020-02-01 12:01:01', '2020-02-01 12:31:01', 82),
(1002, 9002, '2020-02-02 12:11:01', '2020-02-02 12:31:01', 83),
(1005, 9001, '2021-02-01 11:01:01', '2021-02-01 11:31:01', 84),
(1006, 9001, '2021-02-01 11:01:01', '2021-02-01 11:31:01', 84),
(1002, 9001, '2021-09-06 12:01:01', '2021-09-06 12:21:01', 80),
(1002, 9001, '2021-09-06 12:01:01', null, null),
(1002, 9001, '2021-09-07 12:01:01', null, null);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

select
	a.uid
	,a.nick_name
	,a.achievement
from(
		select 
			uid
			,nick_name
			,achievement
		from user_info
		where nick_name like '牛客%号'							#昵称以『牛客』开头『号』结尾、成就值在1200~2500之间
		and achievement between '1200' and '2500'
) a join (
			select  
				uid
			from (
					select 
						uid
						,start_time
						,max(start_time) over(partition by uid) max_time    #最近一次活跃答题在2021年9月
					from exam_record
			) a
			where substr(max_time,1,7)='2021-09'
			group by uid
			union 
			select  
				uid
			from (
					select 
						uid
						,submit_time
						,max(submit_time) over(partition by uid) max_time    #最近一次活跃作答试卷在2021年9月
					from practice_record
			) a
			where substr(max_time,1,7)='2021-09'
			group by uid
) b on a.uid=b.uid
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

【题目2：筛选昵称规则和试卷规则的作答记录】

--输入：
drop table if exists examination_info,user_info,exam_record;
CREATE TABLE examination_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
    tag varchar(32) COMMENT '类别标签',
    difficulty varchar(8) COMMENT '难度',
    duration int NOT NULL COMMENT '时长',
    release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE user_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int UNIQUE NOT NULL COMMENT '用户ID',
    `nick_name` varchar(64) COMMENT '昵称',
    achievement int COMMENT '成就值',
    level int COMMENT '用户等级',
    job varchar(32) COMMENT '职业方向',
    register_time datetime COMMENT '注册时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO user_info(uid,`nick_name`,achievement,level,job,register_time) VALUES
  (1001, '牛客1', 1900, 2, '算法', '2020-01-01 10:00:00'),
  (1002, '牛客2号', 1200, 3, '算法', '2020-01-01 10:00:00'),
  (1003, '牛客3号♂', 2200, 5, '算法', '2020-01-01 10:00:00'),
  (1004, '牛客4号', 2500, 6, '算法', '2020-01-01 10:00:00'),
  (1005, '牛客555号', 2000, 7, 'C++', '2020-01-01 10:00:00'),
  (1006, '666666', 3000, 6, 'C++', '2020-01-01 10:00:00');

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'C++', 'hard', 60, '2020-01-01 10:00:00'),
  (9002, 'c#', 'hard', 80, '2020-01-01 10:00:00'),
  (9003, 'SQL', 'medium', 70, '2020-01-01 10:00:00');
	
INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:59', 80),
(1001, 9001, '2021-05-02 10:01:01', null, null),
(1001, 9002, '2021-02-02 19:01:01', '2021-02-02 19:30:01', 87),
(1001, 9001, '2021-06-02 19:01:01', '2021-06-02 19:32:00', 20),
(1001, 9002, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89),
(1001, 9002, '2021-09-01 12:01:01', null, null),
(1002, 9002, '2021-05-05 18:01:01', '2021-05-05 18:59:02', 90),
(1003, 9003, '2021-02-06 12:01:01', null, null),
(1003, 9001, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 89),
(1004, 9002, '2021-08-06 12:01:01', null, null),
(1002, 9001, '2020-01-01 12:01:01', '2020-01-01 12:31:01', 81),
(1002, 9002, '2020-02-01 12:01:01', '2020-02-01 12:31:01', 82),
(1002, 9002, '2020-02-02 12:11:01', '2020-02-02 12:31:01', 83),
(1005, 9001, '2021-02-01 11:01:01', '2021-02-01 11:31:01', 84),
(1006, 9001, '2021-09-01 11:01:01', '2021-09-01 11:31:01', 84),
(1002, 9001, '2021-09-06 12:01:01', '2021-09-06 12:21:01', 80),
(1002, 9001, '2021-09-06 12:01:01', null, null),
(1002, 9001, '2021-09-07 12:01:01', null, null);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

RLIKE后面可以跟正则表达式
正则表达式" ^ [0-9]+$ "的意思：
1、字符^
意义：表示匹配的字符必须在最前边。
例如：^A不匹配“an A”中的‘A’，但匹配“An A”中最前面的‘A’。
2、字符$
意义：与^类似，匹配最末的字符。
例如：t$不匹配“eater”中的‘t’，但匹配“eat”中的‘t’。
3、字符[0-9]
意义：字符列表，匹配列出中的任一个字符。你可以通过连字符-指出字符范围。
例如：[abc]跟[a-c]一样。它们匹配“brisket”中的‘b’和“ache”中的‘c’。
4、字符+
意义：匹配+号前面的字符1次及以上。等价于{1,}。
例如：a+匹配“candy”中的‘a’和“caaaaaaandy”中的所有‘a’。

select 
	a.uid
	,a.exam_id										 	# 已完成的试卷ID
	,round(avg(a.score),0) avg_score        			# 平均得分
from (
		select  
			uid
			,exam_id
			,score
		from exam_record
		where score is not null       					# 已完成的试卷
) a join (
			select 
				uid
			from user_info
			where (nick_name rlike '^牛客[0-9]+号$')    	# 昵称以"牛客"+纯数字+"号"组成
			or (nick_name rlike '^[0-9]+$')				# 昵称以纯数字组成
) b on a.uid=b.uid
join (
		select 
			exam_id
			,tag
		from examination_info
		where tag rlike '^[Cc]'           				# 字母c开头的试卷类别（如C,C++,c#等）
) c on a.exam_id=c.exam_id
group by a.uid,a.exam_id	
order by a.uid,avg_score
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

【题目3：根据指定记录是否存在输出不同情况】

--输入：
drop table if exists user_info,exam_record;
CREATE TABLE user_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int UNIQUE NOT NULL COMMENT '用户ID',
    `nick_name` varchar(64) COMMENT '昵称',
    achievement int COMMENT '成就值',
    level int COMMENT '用户等级',
    job varchar(32) COMMENT '职业方向',
    register_time datetime COMMENT '注册时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO user_info(uid,`nick_name`,achievement,level,job,register_time) VALUES
  (1001, '牛客1', 19, 0, '算法', '2020-01-01 10:00:00'),
  (1002, '牛客2号', 1200, 3, '算法', '2020-01-01 10:00:00'),
  (1003, '牛客3号♂', 22, 0, '算法', '2020-01-01 10:00:00'),
  (1004, '牛客4号', 25, 0, '算法', '2020-01-01 10:00:00'),
  (1005, '牛客555号', 2000, 7, 'C++', '2020-01-01 10:00:00'),
  (1006, '666666', 3000, 6, 'C++', '2020-01-01 10:00:00');

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:59', 80),
(1001, 9001, '2021-05-02 10:01:01', null, null),
(1001, 9002, '2021-02-02 19:01:01', '2021-02-02 19:30:01', 87),
(1001, 9002, '2021-09-01 12:01:01', null, null),
(1001, 9003, '2021-09-02 12:01:01', null, null),
(1001, 9004, '2021-09-03 12:01:01', null, null),
(1002, 9001, '2020-01-01 12:01:01', '2020-01-01 12:31:01', 99),
(1002, 9003, '2020-02-01 12:01:01', '2020-02-01 12:31:01', 82),
(1002, 9003, '2020-02-02 12:11:01', null, null),
(1002, 9002, '2021-05-05 18:01:01', null, null),
(1002, 9001, '2021-09-06 12:01:01', null, null),
(1003, 9003, '2021-02-06 12:01:01', null, null),
(1003, 9001, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 89);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

with t1 as (
				select 
					a.uid
					,a.level
					,count(b.start_time)   total_cnt   							# 试卷总数	
					,count(b.submit_time)  complete_cnt   						# 完成试卷数	
					,count(b.start_time)-count(b.submit_time)  incomplete_cnt  	# 未完成试卷数																								  
					,ifnull(round((count(b.start_time)-count(b.submit_time))/count(b.start_time),3),0)  incomplete_rate # 未完成率
				from (
						select 
							uid
							,level
						from user_info
				) a left join (
								select 
									uid
									,exam_id
									,start_time
									,submit_time
								from exam_record
				) b on a.uid=b.uid
				group by a.uid,a.level
) 

select 
	uid
	,incomplete_cnt			# 试卷未完成数
	,incomplete_rate		# 试卷未完成率
from t1
where exists (select uid from t1 where level='0' and incomplete_cnt>2)     	# 当有任意一个0级用户未完成试卷数大于2时
and level='0'																# 输出每个0级用户的试卷未完成数和未完成率
union all
select 
	uid
	,incomplete_cnt			# 试卷未完成数
	,incomplete_rate		# 试卷未完成率
from t1
where not exists (select uid from t1 where level='0' and incomplete_cnt>2) 	# 若不存在这样的用户
and total_cnt>0																# 则输出所有有作答记录的用户的这两个指标
order by incomplete_rate
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

【题目4：各用户等级的不同得分表现占比】

--输入：
drop table if exists user_info,exam_record;
CREATE TABLE user_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int UNIQUE NOT NULL COMMENT '用户ID',
    `nick_name` varchar(64) COMMENT '昵称',
    achievement int COMMENT '成就值',
    level int COMMENT '用户等级',
    job varchar(32) COMMENT '职业方向',
    register_time datetime COMMENT '注册时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO user_info(uid,`nick_name`,achievement,level,job,register_time) VALUES
  (1001, '牛客1', 19, 0, '算法', '2020-01-01 10:00:00'),
  (1002, '牛客2号', 1200, 3, '算法', '2020-01-01 10:00:00'),
  (1003, '牛客3号♂', 22, 0, '算法', '2020-01-01 10:00:00'),
  (1004, '牛客4号', 25, 0, '算法', '2020-01-01 10:00:00'),
  (1005, '牛客555号', 2000, 7, 'C++', '2020-01-01 10:00:00'),
  (1006, '666666', 3000, 6, 'C++', '2020-01-01 10:00:00');

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:59', 80),
(1001, 9001, '2021-05-02 10:01:01', null, null),
(1001, 9002, '2021-02-02 19:01:01', '2021-02-02 19:30:01', 75),
(1001, 9002, '2021-09-01 12:01:01', '2021-09-01 12:11:01', 60),
(1001, 9003, '2021-09-02 12:01:01', '2021-09-02 12:41:01', 90),
(1001, 9001, '2021-06-02 19:01:01', '2021-06-02 19:32:00', 20),
(1001, 9002, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89),
(1001, 9004, '2021-09-03 12:01:01', null, null),
(1002, 9001, '2020-01-01 12:01:01', '2020-01-01 12:31:01', 99),
(1002, 9003, '2020-02-01 12:01:01', '2020-02-01 12:31:01', 82),
(1002, 9003, '2020-02-02 12:11:01', '2020-02-02 12:41:01', 76);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

select 
	level
	,score_grade									# 用户等级
	,round(count(score_grade)/level_cnt,3) ratio 	# 各得分等级占比（结果保留3位小数）
from (
		select 
			a.level
			,b.score_grade
			,count(a.level) over(partition by a.level) level_cnt   # 各分数等级的数量
		from (
				select 
					uid
					,level
				from user_info
		) a join (
					select 
						uid
						,score
						,case when score>=90 then '优'
							  when score>=75 then '良'
							  when score>=60 then '中'
							  when score<60  then '差'
						 else null end score_grade             		# 将试卷得分按分界点[90,75,60]分为优良中差四个得分等级（分界点划分到左区间）
					from exam_record 
		) b on a.uid=b.uid   
		where b.score_grade is not null   							# 未完成过试卷的用户无需输出
) a1
group by level,score_grade
order by level desc,ratio desc
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

3. 限量查询

【题目1：注册时间最早的三个人】

--输入：
drop table if exists user_info,exam_record;
CREATE TABLE user_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int UNIQUE NOT NULL COMMENT '用户ID',
    `nick_name` varchar(64) COMMENT '昵称',
    achievement int COMMENT '成就值',
    level int COMMENT '用户等级',
    job varchar(32) COMMENT '职业方向',
    register_time datetime COMMENT '注册时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO user_info(uid,`nick_name`,achievement,level,job,register_time) VALUES
  (1001, '牛客1', 19, 0, '算法', '2020-01-01 10:00:00'),
  (1002, '牛客2号', 1200, 3, '算法', '2020-02-01 10:00:00'),
  (1003, '牛客3号♂', 22, 0, '算法', '2020-01-02 10:00:00'),
  (1004, '牛客4号', 25, 0, '算法', '2020-01-02 11:00:00'),
  (1005, '牛客555号', 4000, 7, 'C++', '2020-01-11 10:00:00'),
  (1006, '666666', 3000, 6, 'C++', '2020-11-01 10:00:00');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

-- 方法1
select 
	uid	
	,nick_name	
	,register_time	
from (
		select 
			uid	
			,nick_name	
			,register_time
			,row_number() over(order by register_time) rn
		from user_info
) a
where rn<=3
;

-- 方法2
select 
	uid
	,nick_name
	,register_time
from user_info
order by register_time
limit 3
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

【题目2：注册当天就完成了试卷的名单三页】

--输入：
drop table if exists examination_info,user_info,exam_record;
CREATE TABLE examination_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
    tag varchar(32) COMMENT '类别标签',
    difficulty varchar(8) COMMENT '难度',
    duration int NOT NULL COMMENT '时长',
    release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE user_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int UNIQUE NOT NULL COMMENT '用户ID',
    `nick_name` varchar(64) COMMENT '昵称',
    achievement int COMMENT '成就值',
    level int COMMENT '用户等级',
    job varchar(32) COMMENT '职业方向',
    register_time datetime COMMENT '注册时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO user_info(uid,`nick_name`,achievement,`level`,job,register_time) VALUES
  (1001, '牛客1', 19, 0, '算法', '2020-01-01 10:00:00'),
  (1002, '牛客2号', 1200, 3, '算法', '2020-01-01 10:00:00'),
  (1003, '牛客3号♂', 22, 0, '算法', '2020-01-01 10:00:00'),
  (1004, '牛客4号', 25, 0, '算法', '2020-01-01 11:00:00'),
  (1005, '牛客555号', 4000, 7, '算法', '2020-01-01 10:00:00'),
  (1006, '牛客6号', 25, 0, '算法', '2020-01-02 11:00:00'),
  (1007, '牛客7号', 25, 0, '算法', '2020-01-02 11:00:00'),
  (1008, '牛客8号', 25, 0, '算法', '2020-01-02 11:00:00'),
  (1009, '牛客9号', 25, 0, '算法', '2020-01-02 11:00:00'),
  (1010, '牛客10号', 25, 0, '算法', '2020-01-02 11:00:00'),
  (1011, '666666', 3000, 6, 'C++', '2020-01-02 10:00:00');

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, '算法', 'hard', 60, '2020-01-01 10:00:00'),
  (9002, '算法', 'hard', 80, '2020-01-01 10:00:00'),
  (9003, 'SQL', 'medium', 70, '2020-01-01 10:00:00');
	
INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-01 09:01:01', '2020-01-01 09:21:59', 80),
(1002, 9003, '2020-01-20 10:01:01', '2020-01-20 10:10:01', 81),
(1002, 9002, '2020-01-01 12:11:01', '2020-01-01 12:31:01', 83),
(1003, 9002, '2020-01-01 19:01:01', '2020-01-01 19:30:01', 75),
(1004, 9002, '2020-01-01 12:01:01', '2020-01-01 12:11:01', 60),
(1005, 9002, '2020-01-01 12:01:01', '2020-01-01 12:41:01', 90),
(1006, 9001, '2020-01-02 19:01:01', '2020-01-02 19:32:00', 20),
(1007, 9002, '2020-01-02 19:01:01', '2020-01-02 19:40:01', 89),
(1008, 9003, '2020-01-02 12:01:01', '2020-01-02 12:20:01', 99),
(1008, 9001, '2020-01-02 12:01:01', '2020-01-02 12:31:01', 98),
(1009, 9002, '2020-01-02 12:01:01', '2020-01-02 12:31:01', 82),
(1010, 9002, '2020-01-02 12:11:01', '2020-01-02 12:41:01', 76),
(1011, 9001, '2020-01-02 10:01:01', '2020-01-02 10:31:01', 89);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

select 
	uid
	,level
	,register_time
	,max_score
from (
		select 
			a.uid
			,b.level
			,b.register_time
			,a.exam_id
			,a.score
			,max(a.score) over(partition by a.uid,a.exam_id) max_score
		from (
				select 
					uid
					,exam_id
					,submit_time
					,score
				from exam_record
		) a join (
					select 
						uid
						,level
						,job
						,register_time
					from user_info
					where job='算法'   						# 求职方向为算法工程师
		) b on a.uid=b.uid
		join (
				select
					exam_id
					,tag
				from examination_info   
				where tag='算法'   							# 算法类试卷
		) c on a.exam_id=c.exam_id
		where date(b.register_time)=date(a.submit_time)   	# 注册当天就完成了算法类试卷
) a1
where score=max_score										# 参加过的所有考试最高得分
order by max_score desc										# 按参加过的所有考试最高得分排名
limit 6,3                   								# 每页3条，取出第3页的人的信息=选取第三页的3条，即偏移/跳过前6条（0为起点），取三条=展示7-9行
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

4. 文本转换函数

【题目1：修复串列了的记录】

--输入：
drop table if exists examination_info,exam_record;
CREATE TABLE examination_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
    tag varchar(32) COMMENT '类别标签',
    difficulty varchar(8) COMMENT '难度',
    duration int NOT NULL COMMENT '时长',
    release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, '算法', 'hard', 60, '2020-01-01 10:00:00'),
  (9002, '算法', 'hard', 80, '2020-01-01 10:00:00'),
  (9003, 'SQL', 'medium', 70, '2020-01-01 10:00:00'),
  (9004, '算法,medium,80','', 0, '2020-01-01 10:00:00');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

select 
	exam_id	
	,substring_index(tag,',',1) tag
	,substring_index(substring_index(tag,',',2),',',-1) difficulty
	,substring_index(tag,',',-1) duration
from examination_info  
where tag like '%,%'     # 找出这些录错了的记录
;

;
1
2
3
4
5
6
7
8
9
10

【题目2：对过长的昵称截取处理】

获取字符串长度
LENTH：获取字符串的字节数量，一个中文字符通常不止占用一个字节
CHAR_LENTH：获取字符数量，不管汉字、字母、数字都算一个字符

select 
	uid
	,if(nick_name_cnt>13,concat(substr(nick_name,1,10),'...'),nick_name)  nick_name   # 对于字符数大于13的用户输出前10个字符然后加上三个点号：
from (
		select 
			uid
			,nick_name
			,char_length(nick_name) nick_name_cnt    # 根据解释（字符数大于10的用户有1005和1006，长度分别为13、17）可知，计算字符数用char_length()
		from user_info  
) a 
where nick_name_cnt>10   # 输出字符数大于10的用户信息
;
1
2
3
4
5
6
7
8
9
10
11
12

【题目3：大小写混乱时的筛选统计】

drop table if exists user_info;
CREATE TABLE user_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int UNIQUE NOT NULL COMMENT '用户ID',
    `nick_name` varchar(64) COMMENT '昵称',
    achievement int COMMENT '成就值',
    level int COMMENT '用户等级',
    job varchar(32) COMMENT '职业方向',
    register_time datetime COMMENT '注册时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO user_info(uid,`nick_name`,achievement,`level`,job,register_time) VALUES
  (1001, '牛客1', 19, 0, '算法', '2020-01-01 10:00:00'),
  (1002, '牛客2号', 1200, 3, '算法', '2020-01-01 10:00:00'),
  (1003, '牛客3号♂', 22, 0, '算法', '2020-01-01 10:00:00'),
  (1004, '牛客4号', 25, 0, '算法', '2020-01-01 11:00:00'),
  (1005, '牛客5678901234号', 4000, 7, '算法', '2020-01-01 10:00:00'),
  (1006, '牛客67890123456789号', 25, 0, '算法', '2020-01-02 11:00:00');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

with t1 as (
				select 
					a.tag  								  # 试卷的类别tag
					,count(b.start_time)    answer_cnt    # 每种类别的试卷作答数
				from (
						select 
							exam_id
							,tag
						from examination_info
				) a join (                                # 根据解释（作答次数小于3的tag有c++和sql）可知，用join 
							select 
								uid
								,exam_id
								,start_time
							from exam_record
				) b on a.exam_id=b.exam_id
				group by a.tag
) 

select 
	a.tag
	,b.answer_cnt
from t1 a
join t1 b 
on upper(a.tag) = b.tag and a.tag != b.tag and a.answer_cnt < 3
;			
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26