持续创作，加速成长！这是我参与「掘金日新计划 · 6 月更文挑战」的第17天，点击查看活动详情
📢📢📢📣📣📣
哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10年DBA工作经验
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程😜😜😜
擅长Oracle、MySQL、PG 运维开发，备份恢复，安装迁移，性能优化、故障应急处理。
❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️
摘要：SQL每个人都要用，但是用来衡量产出的并不是SQL本身，你需要用这个工具，去创造其它的价值。
1 🌈 空值处理
🚀 SQL36 统计有未完成状态的试卷的未完成数和未完成率
📖 exam_record 表结构
🚀 题目描述
现有试卷作答记录表exam_record（uid用户ID, exam_id试卷ID,
start_time开始作答时间, submit_time交卷时间, score得分），数据如下：
+----+------+---------+---------------------+---------------------+-------+
| id | uid | exam_id | start_time | submit_time | score |
+----+------+---------+---------------------+---------------------+-------+
| 1 | 1001 | 9001 | 2020-01-02 09:01:01 | 2020-01-02 09:21:01 | 80 |
| 2 | 1001 | 9001 | 2021-05-02 10:01:01 | 2021-05-02 10:30:01 | 81 |
| 3 | 1001 | 9001 | 2021-09-02 12:01:01 | NULL | NULL |
+----+------+---------+---------------------+---------------------+-------+
请统计有未完成状态的试卷的未完成数incomplete_cnt和未完成率incomplete_rate。
由示例数据结果输出如下：
+---------+----------------+-----------------+
| exam_id | incomplete_cnt | incomplete_rate |
+---------+----------------+-----------------+
| 9001 | 1 | 0.333 |
+---------+----------------+-----------------+
解释：试卷9001有3次被作答的记录，其中两次完成，1次未完成，
因此未完成数为1，未完成率为0.333（保留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-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);
🍌🍌 答案
select exam_id,
sum(case when score is null then 1 else 0 end) incomplete_cnt,
round((count(*)-count(submit_time))/count(*),3) incomplete_rate
from exam_record
group by exam_id
HAVING (count(*)-count(submit_time))<>0;
🚀 SQL37 0级用户高难度试卷的平均用时和平均得分
📖 user_info 表结构
📖 examination_info 表结构
📖 exam_record 表结构
🚀 题目描述
现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间），数据如下：
+----+------+------------+-------------+-------+--------+---------------------+
| id | uid | nick_name | achievement | level | job | register_time |
+----+------+------------+-------------+-------+--------+---------------------+
| 1 | 1001 | 牛客1号 | 10 | 0 | 算法 | 2020-01-01 10:00:00 |
| 2 | 1002 | 牛客2号 | 2100 | 6 | 算法 | 2020-01-01 10:00:00 |
+----+------+------------+-------------+-------+--------+---------------------+
试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间），数据如下：
+----+---------+--------+------------+----------+---------------------+
| id | exam_id | tag | difficulty | duration | release_time |
+----+---------+--------+------------+----------+---------------------+
| 1 | 9001 | SQL | hard | 60 | 2020-01-01 10:00:00 |
| 2 | 9002 | SQL | easy | 60 | 2020-01-01 10:00:00 |
| 3 | 9004 | 算法 | medium | 80 | 2020-01-01 10:00:00 |
+----+---------+--------+------------+----------+---------------------+
试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分），数据如下：
+----+------+---------+---------------------+---------------------+-------+
| id | uid | exam_id | start_time | submit_time | score |
+----+------+---------+---------------------+---------------------+-------+
| 1 | 1001 | 9001 | 2020-01-02 09:01:01 | 2020-01-02 09:21:59 | 80 |
| 2 | 1001 | 9001 | 2021-05-02 10:01:01 | NULL | NULL |
| 3 | 1001 | 9002 | 2021-02-02 19:01:01 | 2021-02-02 19:30:01 | 87 |
| 4 | 1001 | 9001 | 2021-06-02 19:01:01 | 2021-06-02 19:32:00 | 20 |
| 5 | 1001 | 9002 | 2021-09-05 19:01:01 | 2021-09-05 19:40:01 | 89 |
| 6 | 1001 | 9002 | 2021-09-01 12:01:01 | NULL | NULL |
| 7 | 1002 | 9002 | 2021-05-05 18:01:01 | 2021-05-05 18:59:02 | 90 |
+----+------+---------+---------------------+---------------------+-------+
请输出每个0级用户所有的高难度试卷考试平均用时和平均得分，
未完成的默认试卷最大考试时长和0分处理。由示例数据结果输出如下：
+------+-----------+---------------+
| uid | avg_score | avg_time_took |
+------+-----------+---------------+
| 1001 | 33 | 36.7 |
+------+-----------+---------------+
解释：0级用户有1001，高难度试卷有9001，1001作答9001的记录有3条，
分别用时20分钟、未完成（试卷时长60分钟）、30分钟（未满31分钟），分别得分为80分、未完成（0分处理）、20分。
因此他的平均用时为110/3=36.7（保留一位小数），平均得分为33分（取整）
🚀 建表语句
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);
🍌🍌 答案
SELECT
ui.uid,
ROUND(AVG(IFNULL(score,0)),0) avg_score,
ROUND(AVG(IFNULL(TIMESTAMPDIFF(minute,start_time,submit_time),ei.duration)),1) avg_time_took
FROM user_info ui
LEFT JOIN exam_record er USING(uid)
LEFT JOIN examination_info ei USING(exam_id)
WHERE level = 0
AND difficulty = 'hard'
GROUP BY ui.uid;
2 🌈 高级条件语句
🚀 SQL36 统计有未完成状态的试卷的未完成数和未完成率
📖 user_info 表结构
📖 exam_record 表结构
📖 practice_record 表结构
🚀 题目描述
现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）：
+----+------+---------------+-------------+-------+--------+---------------------+
| id | uid | nick_name | achievement | level | job | register_time |
+----+------+---------------+-------------+-------+--------+---------------------+
| 1 | 1001 | 牛客1号 | 1000 | 2 | 算法 | 2020-01-01 10:00:00 |
| 2 | 1002 | 牛客2号 | 1200 | 3 | 算法 | 2020-01-01 10:00:00 |
| 3 | 1003 | 进击的3号 | 2200 | 5 | 算法 | 2020-01-01 10:00:00 |
| 4 | 1004 | 牛客4号 | 2500 | 6 | 算法 | 2020-01-01 10:00:00 |
| 5 | 1005 | 牛客5号 | 3000 | 7 | C++ | 2020-01-01 10:00:00 |
+----+------+---------------+-------------+-------+--------+---------------------+
试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：
+----+------+---------+---------------------+---------------------+-------+
| id | uid | exam_id | start_time | submit_time | score |
+----+------+---------+---------------------+---------------------+-------+
| 1 | 1001 | 9001 | 2020-01-02 09:01:01 | 2020-01-02 09:21:59 | 80 |
| 2 | 1001 | 9001 | 2021-05-02 10:01:01 | NULL | NULL |
| 3 | 1001 | 9002 | 2021-02-02 19:01:01 | 2021-02-02 19:30:01 | 87 |
| 4 | 1001 | 9001 | 2021-06-02 19:01:01 | 2021-06-02 19:32:00 | 20 |
| 5 | 1001 | 9002 | 2021-09-05 19:01:01 | 2021-09-05 19:40:01 | 89 |
| 6 | 1001 | 9002 | 2021-09-01 12:01:01 | NULL | NULL |
| 7 | 1002 | 9002 | 2021-05-05 18:01:01 | 2021-05-05 18:59:02 | 90 |
| 8 | 1003 | 9003 | 2021-02-06 12:01:01 | NULL | NULL |
| 9 | 1003 | 9001 | 2021-09-07 10:01:01 | 2021-09-07 10:31:01 | 89 |
| 10 | 1004 | 9002 | 2021-08-06 12:01:01 | NULL | NULL |
| 11 | 1002 | 9001 | 2020-01-01 12:01:01 | 2020-01-01 12:31:01 | 81 |
| 12 | 1002 | 9002 | 2020-02-01 12:01:01 | 2020-02-01 12:31:01 | 82 |
| 13 | 1002 | 9002 | 2020-02-02 12:11:01 | 2020-02-02 12:31:01 | 83 |
| 14 | 1005 | 9001 | 2021-02-01 11:01:01 | 2021-02-01 11:31:01 | 84 |
| 15 | 1006 | 9001 | 2021-02-01 11:01:01 | 2021-02-01 11:31:01 | 84 |
| 16 | 1002 | 9001 | 2021-09-06 12:01:01 | 2021-09-06 12:21:01 | 80 |
| 17 | 1002 | 9001 | 2021-09-06 12:01:01 | NULL | NULL |
| 18 | 1002 | 9001 | 2021-09-07 12:01:01 | NULL | NULL |
+----+------+---------+---------------------+---------------------+-------+
题目练习记录表practice_record（uid用户ID, question_id题目ID, submit_time提交时间, score得分）：
+----+------+-------------+---------------------+-------+
| id | uid | question_id | submit_time | score |
+----+------+-------------+---------------------+-------+
| 1 | 1001 | 8001 | 2021-08-02 11:41:01 | 60 |
| 2 | 1002 | 8001 | 2021-09-02 19:30:01 | 50 |
| 3 | 1002 | 8001 | 2021-09-02 19:20:01 | 70 |
| 4 | 1002 | 8002 | 2021-09-02 19:38:01 | 70 |
| 5 | 1003 | 8002 | 2021-09-01 19:38:01 | 80 |
+----+------+-------------+---------------------+-------+
请找到昵称以『牛客』开头『号』结尾、成就值在1200~2500之间，且最近一次活跃（答题或作答试卷）在2021年9月的用户信息。
由示例数据结果输出如下：
+------+------------+-------------+
| uid | nick_name | achievement |
+------+------------+-------------+
| 1002 | 牛客2号 | 1200 |
+------+------------+-------------+
解释：昵称以『牛客』开头『号』结尾且成就值在1200~2500之间的有1002、1004；
1002最近一次试卷区活跃为2021年9月，最近一次题目区活跃为2021年9月；
1004最近一次试卷区活跃为2021年8月，题目区未活跃。
因此最终满足条件的只有1002。
🚀 建表语句
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);
🍌🍌 答案
select uid, nick_name, achievement
from(
select distinct A.uid, nick_name, achievement,
max(date_format(start_time,"%y%m")) over(partition by uid order by uid) latest_active
from (
select uid,start_time
from exam_record
union all
select uid, submit_time start_time
from practice_record
) A, user_info ui
where A.uid = ui.uid and substring(nick_name,1,2) = '牛客'
and achievement>=1200 and achievement <= 2500
) X
where latest_active = 2109;
🚀 SQL39 筛选昵称规则和试卷规则的作答记录
📖 user_info 表结构
📖 examination_info 表结构
📖 exam_record 表结构
🚀 题目描述
现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）：
+----+------+---------------+-------------+-------+--------+---------------------+
| id | uid | nick_name | achievement | level | job | register_time |
+----+------+---------------+-------------+-------+--------+---------------------+
| 1 | 1001 | 牛客1 | 1900 | 2 | 算法 | 2020-01-01 10:00:00 |
| 2 | 1002 | 牛客2号 | 1200 | 3 | 算法 | 2020-01-01 10:00:00 |
| 3 | 1003 | 牛客3号♂ | 2200 | 5 | 算法 | 2020-01-01 10:00:00 |
| 4 | 1004 | 牛客4号 | 2500 | 6 | 算法 | 2020-01-01 10:00:00 |
| 5 | 1005 | 牛客555号 | 2000 | 7 | C++ | 2020-01-01 10:00:00 |
| 6 | 1006 | 666666 | 3000 | 6 | C++ | 2020-01-01 10:00:00 |
+----+------+---------------+-------------+-------+--------+---------------------+
试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：
+----+---------+------+------------+----------+---------------------+
| id | exam_id | tag | difficulty | duration | release_time |
+----+---------+------+------------+----------+---------------------+
| 1 | 9001 | C++ | hard | 60 | 2020-01-01 10:00:00 |
| 2 | 9002 | c# | hard | 80 | 2020-01-01 10:00:00 |
| 3 | 9003 | SQL | medium | 70 | 2020-01-01 10:00:00 |
+----+---------+------+------------+----------+---------------------+
试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：
+----+------+---------+---------------------+---------------------+-------+
| id | uid | exam_id | start_time | submit_time | score |
+----+------+---------+---------------------+---------------------+-------+
| 1 | 1001 | 9001 | 2020-01-02 09:01:01 | 2020-01-02 09:21:59 | 80 |
| 2 | 1001 | 9001 | 2021-05-02 10:01:01 | NULL | NULL |
| 3 | 1001 | 9002 | 2021-02-02 19:01:01 | 2021-02-02 19:30:01 | 87 |
| 4 | 1001 | 9001 | 2021-06-02 19:01:01 | 2021-06-02 19:32:00 | 20 |
| 5 | 1001 | 9002 | 2021-09-05 19:01:01 | 2021-09-05 19:40:01 | 89 |
| 6 | 1001 | 9002 | 2021-09-01 12:01:01 | NULL | NULL |
| 7 | 1002 | 9002 | 2021-05-05 18:01:01 | 2021-05-05 18:59:02 | 90 |
| 8 | 1003 | 9003 | 2021-02-06 12:01:01 | NULL | NULL |
| 9 | 1003 | 9001 | 2021-09-07 10:01:01 | 2021-09-07 10:31:01 | 89 |
| 10 | 1004 | 9002 | 2021-08-06 12:01:01 | NULL | NULL |
| 11 | 1002 | 9001 | 2020-01-01 12:01:01 | 2020-01-01 12:31:01 | 81 |
| 12 | 1002 | 9002 | 2020-02-01 12:01:01 | 2020-02-01 12:31:01 | 82 |
| 13 | 1002 | 9002 | 2020-02-02 12:11:01 | 2020-02-02 12:31:01 | 83 |
| 14 | 1005 | 9001 | 2021-02-01 11:01:01 | 2021-02-01 11:31:01 | 84 |
| 15 | 1006 | 9001 | 2021-09-01 11:01:01 | 2021-09-01 11:31:01 | 84 |
| 16 | 1002 | 9001 | 2021-09-06 12:01:01 | 2021-09-06 12:21:01 | 80 |
| 17 | 1002 | 9001 | 2021-09-06 12:01:01 | NULL | NULL |
| 18 | 1002 | 9001 | 2021-09-07 12:01:01 | NULL | NULL |
+----+------+---------+---------------------+---------------------+-------+
找到昵称以"牛客"+纯数字+"号"或者纯数字组成的用户对于字母c开头的试卷类别（如C,C++,c#等）的已完成的试卷ID和平均得分，
按用户ID、平均分升序排序。由示例数据结果输出如下：
+------+---------+-----------+
| uid | exam_id | avg_score |
+------+---------+-----------+
| 1002 | 9001 | 81 |
| 1002 | 9002 | 85 |
| 1005 | 9001 | 84 |
| 1006 | 9001 | 84 |
+------+---------+-----------+
解释：昵称满足条件的用户有1002、1004、1005、1006；
c开头的试卷有9001、9002；
满足上述条件的作答记录中，1002完成9001的得分有81、80，平均分为81（80.5取整四舍五入得81）；
1002完成9002的得分有90、82、83，平均分为85；
🚀 建表语句
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);
🍌🍌 答案
select u_i.uid as uid,
e_r.exam_id as exam_id,
round(avg(score), 0) as avg_score
from exam_record e_r join examination_info e_i
on e_r.exam_id = e_i.exam_id
join user_info u_i
on e_r.uid = u_i.uid
where score is not null
and tag rlike '^(C|c).*'
and (nick_name rlike '^[0-9]+$'
or nick_name rlike '^牛客[0-9]+号$'
)
group by uid, exam_id
order by uid, avg_score;
🚀 SQL40 根据指定记录是否存在输出不同情况
📖 user_info 表结构
📖 exam_record 表结构
🚀 题目描述
现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）：
+----+------+---------------+-------------+-------+--------+---------------------+
| id | uid | nick_name | achievement | level | job | register_time |
+----+------+---------------+-------------+-------+--------+---------------------+
| 1 | 1001 | 牛客1 | 19 | 0 | 算法 | 2020-01-01 10:00:00 |
| 2 | 1002 | 牛客2号 | 1200 | 3 | 算法 | 2020-01-01 10:00:00 |
| 3 | 1003 | 牛客3号♂ | 22 | 0 | 算法 | 2020-01-01 10:00:00 |
| 4 | 1004 | 牛客4号 | 25 | 0 | 算法 | 2020-01-01 10:00:00 |
| 5 | 1005 | 牛客555号 | 2000 | 7 | C++ | 2020-01-01 10:00:00 |
| 6 | 1006 | 666666 | 3000 | 6 | C++ | 2020-01-01 10:00:00 |
+----+------+---------------+-------------+-------+--------+---------------------+
试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：
+----+------+---------+---------------------+---------------------+-------+
| id | uid | exam_id | start_time | submit_time | score |
+----+------+---------+---------------------+---------------------+-------+
| 1 | 1001 | 9001 | 2020-01-02 09:01:01 | 2020-01-02 09:21:59 | 80 |
| 2 | 1001 | 9001 | 2021-05-02 10:01:01 | NULL | NULL |
| 3 | 1001 | 9002 | 2021-02-02 19:01:01 | 2021-02-02 19:30:01 | 87 |
| 4 | 1001 | 9002 | 2021-09-01 12:01:01 | NULL | NULL |
| 5 | 1001 | 9003 | 2021-09-02 12:01:01 | NULL | NULL |
| 6 | 1001 | 9004 | 2021-09-03 12:01:01 | NULL | NULL |
| 7 | 1002 | 9001 | 2020-01-01 12:01:01 | 2020-01-01 12:31:01 | 99 |
| 8 | 1002 | 9003 | 2020-02-01 12:01:01 | 2020-02-01 12:31:01 | 82 |
| 9 | 1002 | 9003 | 2020-02-02 12:11:01 | NULL | NULL |
| 10 | 1002 | 9002 | 2021-05-05 18:01:01 | NULL | NULL |
| 11 | 1002 | 9001 | 2021-09-06 12:01:01 | NULL | NULL |
| 12 | 1003 | 9003 | 2021-02-06 12:01:01 | NULL | NULL |
| 13 | 1003 | 9001 | 2021-09-07 10:01:01 | 2021-09-07 10:31:01 | 89 |
+----+------+---------+---------------------+---------------------+-------+
请你筛选表中的数据，当有任意一个0级用户未完成试卷数大于2时，输出每个0级用户的试卷未完成数和未完成率（保留3位小数）；
若不存在这样的用户，则输出所有有作答记录的用户的这两个指标。结果按未完成率升序排序。
由示例数据结果输出如下：
+------+----------------+-----------------+
| uid | incomplete_cnt | incomplete_rate |
+------+----------------+-----------------+
| 1004 | 0 | 0.000 |
| 1003 | 1 | 0.500 |
| 1001 | 4 | 0.667 |
+------+----------------+-----------------+
解释：0级用户有1001、1003、1004；他们作答试卷数和未完成数分别为：6:4、2:1、0:0；
存在1001这个0级用户未完成试卷数大于2，因此输出这三个用户的未完成数和未完成率（1004未作答过试卷，未完成率默认填0，保留3位小数后是0.000）；
结果按照未完成率升序排序。
附：如果1001不满足『未完成试卷数大于2』，则需要输出1001、1002、1003的这两个指标，因为试卷作答记录表里只有这三个用户的作答记录。
🚀 建表语句
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);
🍌🍌 答案
with t_tag_cnt as (
select b.uid,`level`,ifnull(count(start_time),0) as complete_cnt
,ifnull(count(start_time)-count(score),0) as incomplete_cnt
,max(count(start_time)-count(score)) over(partition by level) as max_incomplete_cnt
from exam_record a
right join user_info b on a.uid = b.uid
group by b.uid,`level`
)
select uid
,incomplete_cnt,round(ifnull(incomplete_cnt/complete_cnt,0),3) as incomplete_rate
from t_tag_cnt where `level`=0 and max_incomplete_cnt>2
union
select uid
,incomplete_cnt,round(ifnull(incomplete_cnt/complete_cnt,0),3) as incomplete_rate
from t_tag_cnt where not EXists( select uid from t_tag_cnt where `level`=0 and max_incomplete_cnt>2)
and complete_cnt != 0
order by incomplete_rate;
🚀 SQL41 各用户等级的不同得分表现占比
📖 user_info 表结构
📖 exam_record 表结构
🚀 题目描述
现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）：
+----+------+---------------+-------------+-------+--------+---------------------+
| id | uid | nick_name | achievement | level | job | register_time |
+----+------+---------------+-------------+-------+--------+---------------------+
| 1 | 1001 | 牛客1 | 19 | 0 | 算法 | 2020-01-01 10:00:00 |
| 2 | 1002 | 牛客2号 | 1200 | 3 | 算法 | 2020-01-01 10:00:00 |
| 3 | 1003 | 牛客3号♂ | 22 | 0 | 算法 | 2020-01-01 10:00:00 |
| 4 | 1004 | 牛客4号 | 25 | 0 | 算法 | 2020-01-01 10:00:00 |
| 5 | 1005 | 牛客555号 | 2000 | 7 | C++ | 2020-01-01 10:00:00 |
| 6 | 1006 | 666666 | 3000 | 6 | C++ | 2020-01-01 10:00:00 |
+----+------+---------------+-------------+-------+--------+---------------------+
试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：
+----+------+---------+---------------------+---------------------+-------+
| id | uid | exam_id | start_time | submit_time | score |
+----+------+---------+---------------------+---------------------+-------+
| 1 | 1001 | 9001 | 2020-01-02 09:01:01 | 2020-01-02 09:21:59 | 80 |
| 2 | 1001 | 9001 | 2021-05-02 10:01:01 | NULL | NULL |
| 3 | 1001 | 9002 | 2021-02-02 19:01:01 | 2021-02-02 19:30:01 | 75 |
| 4 | 1001 | 9002 | 2021-09-01 12:01:01 | 2021-09-01 12:11:01 | 60 |
| 5 | 1001 | 9003 | 2021-09-02 12:01:01 | 2021-09-02 12:41:01 | 90 |
| 6 | 1001 | 9001 | 2021-06-02 19:01:01 | 2021-06-02 19:32:00 | 20 |
| 7 | 1001 | 9002 | 2021-09-05 19:01:01 | 2021-09-05 19:40:01 | 89 |
| 8 | 1001 | 9004 | 2021-09-03 12:01:01 | NULL | NULL |
| 9 | 1002 | 9001 | 2020-01-01 12:01:01 | 2020-01-01 12:31:01 | 99 |
| 10 | 1002 | 9003 | 2020-02-01 12:01:01 | 2020-02-01 12:31:01 | 82 |
| 11 | 1002 | 9003 | 2020-02-02 12:11:01 | 2020-02-02 12:41:01 | 76 |
+----+------+---------+---------------------+---------------------+-------+
为了得到用户试卷作答的定性表现，我们将试卷得分按分界点[90,75,60]分为优良中差四个得分等级（分界点划分到左区间），
请统计不同用户等级的人在完成过的试卷中各得分等级占比（结果保留3位小数），未完成过试卷的用户无需输出，
结果按用户等级降序、占比降序排序。
由示例数据结果输出如下：
+-------+-------------+-------+
| level | score_grade | ratio |
+-------+-------------+-------+
| 3 | 良 | 0.667 |
| 3 | 优 | 0.333 |
| 0 | 良 | 0.500 |
| 0 | 中 | 0.167 |
| 0 | 优 | 0.167 |
| 0 | 差 | 0.167 |
+-------+-------------+-------+
🚀 建表语句
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);
🍌🍌 答案
WITH s1 AS (SELECT exam_record.uid , level , score,
CASE
WHEN score<60 THEN '差'
WHEN score<75 THEN '中'
WHEN score<90 THEN '良'
ELSE '优' END score_grade
, COUNT(*) over(PARTITION BY level) cnt_level
FROM exam_record LEFT JOIN user_info
USING(uid)
WHERE score IS NOT NULL)
SELECT level , score_grade , round(COUNT(uid)/cnt_level,3) ratio
FROM
s1
GROUP BY level,score_grade
ORDER BY level desc , ratio desc;
3 🌈 限量查询
🚀 SQL42 注册时间最早的三个人
📖 user_info 表结构
🚀 题目描述
现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）：
+----+------+---------------+-------------+-------+--------+---------------------+
| id | uid | nick_name | achievement | level | job | register_time |
+----+------+---------------+-------------+-------+--------+---------------------+
| 1 | 1001 | 牛客1 | 19 | 0 | 算法 | 2020-01-01 10:00:00 |
| 2 | 1002 | 牛客2号 | 1200 | 3 | 算法 | 2020-02-01 10:00:00 |
| 3 | 1003 | 牛客3号♂ | 22 | 0 | 算法 | 2020-01-02 10:00:00 |
| 4 | 1004 | 牛客4号 | 25 | 0 | 算法 | 2020-01-02 11:00:00 |
| 5 | 1005 | 牛客555号 | 4000 | 7 | C++ | 2020-01-11 10:00:00 |
| 6 | 1006 | 666666 | 3000 | 6 | C++ | 2020-11-01 10:00:00 |
+----+------+---------------+-------------+-------+--------+---------------------+
请从中找到注册时间最早的3个人。由示例数据结果输出如下：
+------+---------------+---------------------+
| uid | nick_name | register_time |
+------+---------------+---------------------+
| 1001 | 牛客1 | 2020-01-01 10:00:00 |
| 1003 | 牛客3号♂ | 2020-01-02 10:00:00 |
| 1004 | 牛客4号 | 2020-01-02 11:00:00 |
+------+---------------+---------------------+
解释：按注册时间排序后选取前三名，输出其用户ID、昵称、注册时间。
🚀 建表语句
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');
🍌🍌 答案
select uid, nick_name, register_time
from
user_info
order by register_time
limit 3;
🚀 SQL39 筛选昵称规则和试卷规则的作答记录
📖 user_info 表结构
📖 examination_info 表结构
📖 exam_record 表结构
🚀 题目描述
现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）：
+----+------+---------------+-------------+-------+--------+---------------------+
| id | uid | nick_name | achievement | level | job | register_time |
+----+------+---------------+-------------+-------+--------+---------------------+
| 1 | 1001 | 牛客1 | 19 | 0 | 算法 | 2020-01-01 10:00:00 |
| 2 | 1002 | 牛客2号 | 1200 | 3 | 算法 | 2020-01-01 10:00:00 |
| 3 | 1003 | 牛客3号♂ | 22 | 0 | 算法 | 2020-01-01 10:00:00 |
| 4 | 1004 | 牛客4号 | 25 | 0 | 算法 | 2020-01-01 11:00:00 |
| 5 | 1005 | 牛客555号 | 4000 | 7 | 算法 | 2020-01-01 10:00:00 |
| 6 | 1006 | 牛客6号 | 25 | 0 | 算法 | 2020-01-02 11:00:00 |
| 7 | 1007 | 牛客7号 | 25 | 0 | 算法 | 2020-01-02 11:00:00 |
| 8 | 1008 | 牛客8号 | 25 | 0 | 算法 | 2020-01-02 11:00:00 |
| 9 | 1009 | 牛客9号 | 25 | 0 | 算法 | 2020-01-02 11:00:00 |
| 10 | 1010 | 牛客10号 | 25 | 0 | 算法 | 2020-01-02 11:00:00 |
| 11 | 1011 | 666666 | 3000 | 6 | C++ | 2020-01-02 10:00:00 |
+----+------+---------------+-------------+-------+--------+---------------------+
试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：
+----+---------+--------+------------+----------+---------------------+
| id | exam_id | tag | difficulty | duration | release_time |
+----+---------+--------+------------+----------+---------------------+
| 1 | 9001 | 算法 | hard | 60 | 2020-01-01 10:00:00 |
| 2 | 9002 | 算法 | hard | 80 | 2020-01-01 10:00:00 |
| 3 | 9003 | SQL | medium | 70 | 2020-01-01 10:00:00 |
+----+---------+--------+------------+----------+---------------------+
试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：
+----+------+---------+---------------------+---------------------+-------+
| id | uid | exam_id | start_time | submit_time | score |
+----+------+---------+---------------------+---------------------+-------+
| 1 | 1001 | 9001 | 2020-01-01 09:01:01 | 2020-01-01 09:21:59 | 80 |
| 2 | 1002 | 9003 | 2020-01-20 10:01:01 | 2020-01-20 10:10:01 | 81 |
| 3 | 1002 | 9002 | 2020-01-01 12:11:01 | 2020-01-01 12:31:01 | 83 |
| 4 | 1003 | 9002 | 2020-01-01 19:01:01 | 2020-01-01 19:30:01 | 75 |
| 5 | 1004 | 9002 | 2020-01-01 12:01:01 | 2020-01-01 12:11:01 | 60 |
| 6 | 1005 | 9002 | 2020-01-01 12:01:01 | 2020-01-01 12:41:01 | 90 |
| 7 | 1006 | 9001 | 2020-01-02 19:01:01 | 2020-01-02 19:32:00 | 20 |
| 8 | 1007 | 9002 | 2020-01-02 19:01:01 | 2020-01-02 19:40:01 | 89 |
| 9 | 1008 | 9003 | 2020-01-02 12:01:01 | 2020-01-02 12:20:01 | 99 |
| 10 | 1008 | 9001 | 2020-01-02 12:01:01 | 2020-01-02 12:31:01 | 98 |
| 11 | 1009 | 9002 | 2020-01-02 12:01:01 | 2020-01-02 12:31:01 | 82 |
| 12 | 1010 | 9002 | 2020-01-02 12:11:01 | 2020-01-02 12:41:01 | 76 |
| 13 | 1011 | 9001 | 2020-01-02 10:01:01 | 2020-01-02 10:31:01 | 89 |
+----+------+---------+---------------------+---------------------+-------+
找到求职方向为算法工程师，且注册当天就完成了算法类试卷的人，按参加过的所有考试最高得分排名。
排名榜很长，我们将采用分页展示，每页3条，现在需要你取出第3页（页码从1开始）的人的信息。
由示例数据结果输出如下：
+------+-------+---------------------+-----------+
| uid | level | register_time | max_score |
+------+-------+---------------------+-----------+
| 1010 | 0 | 2020-01-02 11:00:00 | 76 |
| 1003 | 0 | 2020-01-01 10:00:00 | 75 |
| 1004 | 0 | 2020-01-01 11:00:00 | 60 |
+------+-------+---------------------+-----------+
🚀 建表语句
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);
🍌🍌 答案
select a.uid,b.level,b.register_time,max(a.score) as max_score from exam_record a
inner join user_info b
on a.uid=b.uid
inner join examination_info c
on a.exam_id=c.exam_id
GROUP by a.uid,b.level,b.register_time
having a.uid in(select a.uid from exam_record a
inner join user_info b
on a.uid=b.uid
inner join examination_info c
on a.exam_id=c.exam_id
where b.job='算法' and c.tag='算法' and
DATE_FORMAT(a.submit_time,'%Y%m%d')=DATE_FORMAT(b.register_time,'%Y%m%d'))
order by max_score desc
limit 6,3;
4 🌈 文本转换函数
🚀 SQL39 筛选昵称规则和试卷规则的作答记录
📖 examination_info 表结构
🚀 题目描述
现有试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：
+----+---------+------------------+------------+----------+---------------------+
| id | exam_id | tag | difficulty | duration | release_time |
+----+---------+------------------+------------+----------+---------------------+
| 1 | 9001 | 算法 | hard | 60 | 2020-01-01 10:00:00 |
| 2 | 9002 | 算法 | hard | 80 | 2020-01-01 10:00:00 |
| 3 | 9003 | SQL | medium | 70 | 2020-01-01 10:00:00 |
| 4 | 9004 | 算法,medium,80 | | 0 | 2020-01-01 10:00:00 |
+----+---------+------------------+------------+----------+---------------------+
录题同学有一次手误将部分记录的试题类别tag、难度、时长同时录入到了tag字段，
请帮忙找出这些录错了的记录，并拆分后按正确的列类型输出。
由示例数据结果输出如下：
+---------+--------+------------+----------+
| exam_id | tag | difficulty | duration |
+---------+--------+------------+----------+
| 9004 | 算法 | medium | 80 |
+---------+--------+------------+----------+
🚀 建表语句
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');
🍌🍌 答案
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 difficulty='';
🚀 SQL45 对过长的昵称截取处理
📖 examination_info 表结构
🚀 题目描述
现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）：
+----+------+-------------------------+-------------+-------+--------+---------------------+
| id | uid | nick_name | achievement | level | job | register_time |
+----+------+-------------------------+-------------+-------+--------+---------------------+
| 1 | 1001 | 牛客1 | 19 | 0 | 算法 | 2020-01-01 10:00:00 |
| 2 | 1002 | 牛客2号 | 1200 | 3 | 算法 | 2020-01-01 10:00:00 |
| 3 | 1003 | 牛客3号♂ | 22 | 0 | 算法 | 2020-01-01 10:00:00 |
| 4 | 1004 | 牛客4号 | 25 | 0 | 算法 | 2020-01-01 11:00:00 |
| 5 | 1005 | 牛客5678901234号 | 4000 | 7 | 算法 | 2020-01-01 10:00:00 |
| 6 | 1006 | 牛客67890123456789号 | 25 | 0 | 算法 | 2020-01-02 11:00:00 |
+----+------+-------------------------+-------------+-------+--------+---------------------+
有的用户的昵称特别长，在一些展示场景会导致样式混乱，因此需要将特别长的昵称转换一下再输出，
请输出字符数大于10的用户信息，对于字符数大于13的用户输出前10个字符然后加上三个点号：『...』。
由示例数据结果输出如下：
+------+---------------------+
| uid | nick_name |
+------+---------------------+
| 1005 | 牛客5678901234号 |
| 1006 | 牛客67890123... |
+------+---------------------+
解释：字符数大于10的用户有1005和1006，长度分别为13、17；因此需要对1006的昵称截断输出。
🚀 建表语句
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');
🍌🍌 答案
SELECT uid, IF(
CHAR_LENGTH(nick_name) > 13, CONCAT(LEFT(nick_name, 10), "..."), nick_name
) nick_name
FROM user_info
WHERE CHAR_LENGTH(nick_name) > 10;
🚀 SQL45 对过长的昵称截取处理
📖 examination_info 表结构
📖 exam_record 表结构
🚀 题目描述
现有试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：
+----+---------+--------+------------+----------+---------------------+
| id | exam_id | tag | difficulty | duration | release_time |
+----+---------+--------+------------+----------+---------------------+
| 1 | 9001 | 算法 | hard | 60 | 2020-01-01 10:00:00 |
| 2 | 9002 | C++ | hard | 80 | 2020-01-01 10:00:00 |
| 3 | 9003 | c++ | hard | 80 | 2020-01-01 10:00:00 |
| 4 | 9004 | sql | medium | 70 | 2020-01-01 10:00:00 |
| 5 | 9005 | C++ | hard | 80 | 2020-01-01 10:00:00 |
| 6 | 9006 | C++ | hard | 80 | 2020-01-01 10:00:00 |
| 7 | 9007 | C++ | hard | 80 | 2020-01-01 10:00:00 |
| 8 | 9008 | SQL | medium | 70 | 2020-01-01 10:00:00 |
| 9 | 9009 | SQL | medium | 70 | 2020-01-01 10:00:00 |
| 10 | 9010 | SQL | medium | 70 | 2020-01-01 10:00:00 |
+----+---------+--------+------------+----------+---------------------+
试卷作答信息表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：
+----+------+---------+---------------------+---------------------+-------+
| id | uid | exam_id | start_time | submit_time | score |
+----+------+---------+---------------------+---------------------+-------+
| 1 | 1001 | 9001 | 2020-01-01 09:01:01 | 2020-01-01 09:21:59 | 80 |
| 2 | 1002 | 9003 | 2020-01-20 10:01:01 | 2020-01-20 10:10:01 | 81 |
| 3 | 1002 | 9002 | 2020-01-01 12:11:01 | 2020-01-01 12:31:01 | 83 |
| 4 | 1003 | 9002 | 2020-01-01 19:01:01 | 2020-01-01 19:30:01 | 75 |
| 5 | 1004 | 9002 | 2020-01-01 12:01:01 | 2020-01-01 12:11:01 | 60 |
| 6 | 1005 | 9002 | 2020-01-01 12:01:01 | 2020-01-01 12:41:01 | 90 |
| 7 | 1006 | 9001 | 2020-01-02 19:01:01 | 2020-01-02 19:32:00 | 20 |
| 8 | 1007 | 9003 | 2020-01-02 19:01:01 | 2020-01-02 19:40:01 | 89 |
| 9 | 1008 | 9004 | 2020-01-02 12:01:01 | 2020-01-02 12:20:01 | 99 |
| 10 | 1008 | 9001 | 2020-01-02 12:01:01 | 2020-01-02 12:31:01 | 98 |
| 11 | 1009 | 9002 | 2020-01-02 12:01:01 | 2020-01-02 12:43:01 | 81 |
| 12 | 1010 | 9002 | 2020-01-02 12:11:01 | NULL | NULL |
| 13 | 1011 | 9001 | 2020-01-02 10:01:01 | 2020-01-02 10:31:01 | 89 |
+----+------+---------+---------------------+---------------------+-------+
试卷的类别tag可能出现大小写混乱的情况，请先筛选出试卷作答数小于3的类别tag，统计将其转换为大写后对应的原本试卷作答数。
如果转换后tag并没有发生变化，不输出该条结果。
由示例数据结果输出如下：
+------+------------+
| tag | answer_cnt |
+------+------------+
| c++ | 6 |
+------+------------+
🚀 建表语句
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, '算法', 'hard', 60, '2020-01-01 10:00:00'),
(9002, 'C++', 'hard', 80, '2020-01-01 10:00:00'),
(9003, 'c++', 'hard', 80, '2020-01-01 10:00:00'),
(9004, 'sql', 'medium', 70, '2020-01-01 10:00:00'),
(9005, 'C++', 'hard', 80, '2020-01-01 10:00:00'),
(9006, 'C++', 'hard', 80, '2020-01-01 10:00:00'),
(9007, 'C++', 'hard', 80, '2020-01-01 10:00:00'),
(9008, 'SQL', 'medium', 70, '2020-01-01 10:00:00'),
(9009, 'SQL', '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', 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, 9003, '2020-01-02 19:01:01', '2020-01-02 19:40:01', 89),
(1008, 9004, '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:43:01', 81),
(1010, 9002, '2020-01-02 12:11:01', null, null),
(1011, 9001, '2020-01-02 10:01:01', '2020-01-02 10:31:01', 89);
🍌🍌 答案
WITH t_tag_count as (
SELECT tag, COUNT(uid) as answer_cnt
FROM exam_record
LEFT JOIN examination_info USING(exam_id)
GROUP BY tag
)
SELECT a.tag, b.answer_cnt
FROM t_tag_count as a
JOIN t_tag_count as b
ON UPPER(a.tag) = b.tag and a.tag != b.tag and a.answer_cnt < 3;
相关推荐阅读：
MySQL8.0新特性抢先看，性能又双叒提升了
MySQL武林秘籍，SQL学废必过考试
Linux7.6源码安装Mysql8
Oracle巡检脚本大全，服务器可直接部署
MySQL root密码忘记找回妙招
监控神器Zabbix，从部署到应用
Oracle监听日志清除
体系化学习SQL，请关注CSDN博客 blog.csdn.net/weixin_4164…
