📢📢📢📣📣📣
哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10年DBA工作经验
一位上进心十足的【大数据领域博主】！😜😜😜
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程
擅长主流数据Oracle、MySQL、PG 运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。
✨ 如果有对【数据库】感兴趣的【小可爱】，欢迎关注【IT邦德】💞💞💞
❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️
文章目录
前言
SQL每个人都要用，但是用来衡量产出的并不是SQL本身，你需要用这个工具，去创造其它的价值。
1 🌈 嵌套子查询
🚀 SQL20 月均完成试卷数不小于3的用户爱作答的类别
📖 examination_info表结构
📖 exam_record表结构
🚀 题目描述
现有试卷作答记录表exam_record（uid：用户ID, exam_id：试卷ID, start_time：开始作答时间, submit_time：交卷时间，没提交的话为NULL, score：得分），示例数据如下：
+----+------+---------+---------------------+---------------------+-------+
| id | uid | exam_id | start_time | submit_time | score |
+----+------+---------+---------------------+---------------------+-------+
| 1 | 1001 | 9001 | 2021-07-02 09:01:01 | NULL | NULL |
| 2 | 1002 | 9003 | 2021-09-01 12:01:01 | 2021-09-01 12:21:01 | 60 |
| 3 | 1002 | 9002 | 2021-09-02 12:01:01 | 2021-09-02 12:31:01 | 70 |
| 4 | 1002 | 9001 | 2021-09-05 19:01:01 | 2021-09-05 19:40:01 | 81 |
| 5 | 1002 | 9002 | 2021-07-06 12:01:01 | NULL | NULL |
| 6 | 1003 | 9003 | 2021-09-07 10:01:01 | 2021-09-07 10:31:01 | 86 |
| 7 | 1003 | 9003 | 2021-09-08 12:01:01 | 2021-09-08 12:11:01 | 40 |
| 8 | 1003 | 9001 | 2021-09-08 13:01:01 | NULL | NULL |
| 9 | 1003 | 9002 | 2021-09-08 14:01:01 | NULL | NULL |
| 10 | 1003 | 9003 | 2021-09-08 15:01:01 | NULL | NULL |
| 11 | 1005 | 9001 | 2021-09-01 12:01:01 | 2021-09-01 12:31:01 | 88 |
| 12 | 1005 | 9002 | 2021-09-01 12:01:01 | 2021-09-01 12:31:01 | 88 |
| 13 | 1005 | 9002 | 2021-09-02 12:11:01 | 2021-09-02 12:31:01 | 89 |
+----+------+---------+---------------------+---------------------+-------+
试卷信息表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 | C++ | easy | 60 | 2020-02-01 10:00:00 |
| 3 | 9003 | 算法 | medium | 80 | 2020-08-02 10:00:00 |
+----+---------+--------+------------+----------+---------------------+
请从表中统计出 “当月均完成试卷数”不小于3的用户们爱作答的类别及作答次数，按次数降序输出，示例输出如下：
解释：用户1002和1005在2021年09月的完成试卷数目均为3，其他用户均小于3；
然后用户1002和1005作答过的试卷tag分布结果按作答次数降序排序依次为C++、SQL、算法。
🚀 建表语句
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);
🍌🍌 答案
select tag, count(start_time) as tag_cnt
from exam_record er inner join examination_info ei
on er.exam_id = ei.exam_id
where uid in
(select uid
from exam_record er
group by uid, month(start_time)
having count(submit_time) >= 3)
group by tag
order by tag_cnt desc;
🚀 SQL21 试卷发布当天作答人数和平均分
📖 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号 | 3100 | 7 | 算法 | 2020-01-01 10:00:00 |
| 2 | 1002 | 牛客2号 | 2100 | 6 | 算法 | 2020-01-01 10:00:00 |
| 3 | 1003 | 牛客3号 | 1500 | 5 | 算法 | 2020-01-01 10:00:00 |
| 4 | 1004 | 牛客4号 | 1100 | 4 | 算法 | 2020-01-01 10:00:00 |
| 5 | 1005 | 牛客5号 | 1600 | 6 | C++ | 2020-01-01 10:00:00 |
| 6 | 1006 | 牛客6号 | 3000 | 6 | C++ | 2020-01-01 10:00:00 |
+----+------+------------+-------------+-------+--------+---------------------+
释义：用户1001昵称为牛客1号，成就值为3100，用户等级是7级，职业方向为算法，注册时间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 | 2021-09-01 06:00:00 |
| 2 | 9002 | C++ | easy | 60 | 2020-02-01 10:00:00 |
| 3 | 9003 | 算法 | medium | 80 | 2020-08-02 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 | 2021-09-01 09:01:01 | 2021-09-01 09:41:01 | 70 |
| 2 | 1002 | 9003 | 2021-09-01 12:01:01 | 2021-09-01 12:21:01 | 60 |
| 3 | 1002 | 9002 | 2021-09-02 12:01:01 | 2021-09-02 12:31:01 | 70 |
| 4 | 1002 | 9001 | 2021-09-01 19:01:01 | 2021-09-01 19:40:01 | 80 |
| 5 | 1002 | 9003 | 2021-08-01 12:01:01 | 2021-08-01 12:21:01 | 60 |
| 6 | 1002 | 9002 | 2021-08-02 12:01:01 | 2021-08-02 12:31:01 | 70 |
| 7 | 1002 | 9001 | 2021-09-01 19:01:01 | 2021-09-01 19:40:01 | 85 |
| 8 | 1002 | 9002 | 2021-07-06 12:01:01 | NULL | NULL |
| 9 | 1003 | 9003 | 2021-09-07 10:01:01 | 2021-09-07 10:31:01 | 86 |
| 10 | 1003 | 9003 | 2021-09-08 12:01:01 | 2021-09-08 12:11:01 | 40 |
| 11 | 1003 | 9001 | 2021-09-01 13:01:01 | 2021-09-01 13:41:01 | 70 |
| 12 | 1003 | 9002 | 2021-09-08 14:01:01 | NULL | NULL |
| 13 | 1003 | 9003 | 2021-09-08 15:01:01 | NULL | NULL |
| 14 | 1005 | 9001 | 2021-09-01 12:01:01 | 2021-09-01 12:31:01 | 90 |
| 15 | 1005 | 9002 | 2021-09-01 12:01:01 | 2021-09-01 12:31:01 | 88 |
| 16 | 1005 | 9002 | 2021-09-02 12:11:01 | 2021-09-02 12:31:01 | 89 |
+----+------+---------+---------------------+---------------------+-------+
请计算每张SQL类别试卷发布后，当天5级以上的用户作答的人数uv和平均分avg_score，按人数降序，相同人数的按平均分升序，示例数据结果输出如下：
+---------+----+-----------+
| exam_id | uv | avg_score |
+---------+----+-----------+
| 9001 | 3 | 81.3 |
+---------+----+-----------+
解释：只有一张SQL类别的试卷，试卷ID为9001，发布当天（2021-09-01）有1001、1002、1003、1005作答过，
但是1003是5级用户，其他3位为5级以上，他们三的得分有[70,80,85,90]，平均分为81.3（保留1位小数）。
🚀 建表语句
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);
🍌🍌 答案
SELECT
exam_id,
count( DISTINCT uid ) AS uv,
ROUND(avg( score ), 1) AS avg_score
FROM exam_record
WHERE (exam_id, DATE(start_time)) IN (
SELECT exam_id, DATE(release_time)
FROM examination_info WHERE tag = "SQL"
) AND uid IN ( SELECT uid FROM user_info WHERE `level` > 5 )
GROUP BY exam_id
ORDER BY uv DESC, avg_score ASC;
备注：
结果按人数uv降序，相同人数的按平均分升序
🚀 SQL22 作答试卷得分大于过80的人的用户等级分布
📖 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号 | 3100 | 7 | 算法 | 2020-01-01 10:00:00 |
| 2 | 1002 | 牛客2号 | 2100 | 6 | 算法 | 2020-01-01 10:00:00 |
| 3 | 1003 | 牛客3号 | 1500 | 5 | 算法 | 2020-01-01 10:00:00 |
| 4 | 1004 | 牛客4号 | 1100 | 4 | 算法 | 2020-01-01 10:00:00 |
| 5 | 1005 | 牛客5号 | 1600 | 6 | C++ | 2020-01-01 10:00:00 |
| 6 | 1006 | 牛客6号 | 3000 | 6 | C++ | 2020-01-01 10:00:00 |
+----+------+------------+-------------+-------+--------+---------------------+
释义：用户1001昵称为牛客1号，成就值为3100，用户等级是7级，职业方向为算法，注册时间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 | 2021-09-01 06:00:00 |
| 2 | 9002 | C++ | easy | 60 | 2020-02-01 10:00:00 |
| 3 | 9003 | 算法 | medium | 80 | 2020-08-02 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 | 2021-09-01 09:01:01 | 2021-09-01 09:41:01 | 70 |
| 2 | 1002 | 9003 | 2021-09-01 12:01:01 | 2021-09-01 12:21:01 | 60 |
| 3 | 1002 | 9002 | 2021-09-02 12:01:01 | 2021-09-02 12:31:01 | 70 |
| 4 | 1002 | 9001 | 2021-09-01 19:01:01 | 2021-09-01 19:40:01 | 80 |
| 5 | 1002 | 9003 | 2021-08-01 12:01:01 | 2021-08-01 12:21:01 | 60 |
| 6 | 1002 | 9002 | 2021-08-02 12:01:01 | 2021-08-02 12:31:01 | 70 |
| 7 | 1002 | 9001 | 2021-09-01 19:01:01 | 2021-09-01 19:40:01 | 85 |
| 8 | 1002 | 9002 | 2021-07-06 12:01:01 | NULL | NULL |
| 9 | 1003 | 9003 | 2021-09-07 10:01:01 | 2021-09-07 10:31:01 | 86 |
| 10 | 1003 | 9003 | 2021-09-08 12:01:01 | 2021-09-08 12:11:01 | 40 |
| 11 | 1003 | 9001 | 2021-09-01 13:01:01 | 2021-09-01 13:41:01 | 70 |
| 12 | 1003 | 9002 | 2021-09-08 14:01:01 | NULL | NULL |
| 13 | 1003 | 9003 | 2021-09-08 15:01:01 | NULL | NULL |
| 14 | 1005 | 9001 | 2021-09-01 12:01:01 | 2021-09-01 12:31:01 | 90 |
| 15 | 1005 | 9002 | 2021-09-01 12:01:01 | 2021-09-01 12:31:01 | 88 |
| 16 | 1005 | 9002 | 2021-09-02 12:11:01 | 2021-09-02 12:31:01 | 89 |
+----+------+---------+---------------------+---------------------+-------+
统计作答SQL类别的试卷得分大于过80的人的用户等级分布，按数量降序排序（保证数量都不同）。
示例数据结果输出如下：
解释：9001为SQL类试卷，作答该试卷大于80分的人有1002、1003、1005共3人，6级两人，5级一人。
🚀 建表语句
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);
🍌🍌 答案
SELECT level, COUNT(DISTINCT uid) AS level_cnt
FROM exam_record er JOIN user_info ui USING(uid)
JOIN examination_info ei USING(exam_id)
WHERE ei.tag='SQL' AND er.score > 80
GROUP BY level
ORDER BY COUNT(DISTINCT uid) DESC, level DESC;
2 🌈 合并查询
🚀 SQL23 每个题目和每份试卷被作答的人数和次数
📖 exam_record表结构
📖 practice_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 | 2021-09-01 09:01:01 | 2021-09-01 09:41:01 | 81 |
| 2 | 1002 | 9002 | 2021-09-01 12:01:01 | 2021-09-01 12:31:01 | 70 |
| 3 | 1002 | 9001 | 2021-09-01 19:01:01 | 2021-09-01 19:40:01 | 80 |
| 4 | 1002 | 9002 | 2021-09-01 12:01:01 | 2021-09-01 12:31:01 | 70 |
| 5 | 1004 | 9001 | 2021-09-01 19:01:01 | 2021-09-01 19:40:01 | 85 |
| 6 | 1002 | 9002 | 2021-09-01 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 | 8001 | 2021-08-02 19:38:01 | 70 |
| 6 | 1003 | 8001 | 2021-08-02 19:48:01 | 90 |
| 7 | 1003 | 8002 | 2021-08-01 19:38:01 | 80 |
+----+------+-------------+---------------------+-------+
请统计每个题目和每份试卷被作答的人数和次数，分别按照"试卷"和"题目"的uv & pv降序显示，示例数据结果输出如下：
+------+----+----+
| tid | uv | pv |
+------+----+----+
| 9001 | 3 | 3 |
| 9002 | 1 | 3 |
| 8001 | 3 | 5 |
| 8002 | 2 | 2 |
+------+----+----+
解释：“试卷”有3人共练习3次试卷9001，1人作答3次9002；“刷题”有3人刷5次8001，有2人刷2次8002
🚀 建表语句
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);
🍌🍌 答案
SELECT exam_id as tid,
count(distinct uid) as uv,
count(uid) as pv
from exam_record
group by exam_id
union ALL
SELECT question_id as tid,
count(distinct uid) as uv,
count(uid) as pv
from practice_record
group by question_id
order by LEFT(tid,1) DESC,uv DESC,pv DESC;
🚀 SQL24 分别满足两个活动的人
📖 examination_info表结构
📖 exam_record表结构
🚀 题目描述
为了促进更多用户在牛客平台学习和刷题进步，我们会经常给一些既活跃又表现不错的用户发放福利。
假使以前我们有两拨运营活动，分别给每次试卷得分都能到85分的人（activity1）、
至少有一次用了一半时间就完成高难度试卷且分数大于80的人（activity2）发了福利券。
现在，需要你一次性将这两个活动满足的人筛选出来，交给运营同学。
请写出一个SQL实现：输出2021年里，所有每次试卷得分都能到85分的人以及至少有一次用了一半时间就完成高难度试卷且分数大于80的人的id和活动号，
按用户ID排序输出。
现有试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：
+----+---------+--------+------------+----------+---------------------+
| id | exam_id | tag | difficulty | duration | release_time |
+----+---------+--------+------------+----------+---------------------+
| 1 | 9001 | SQL | hard | 60 | 2021-09-01 06:00:00 |
| 2 | 9002 | C++ | hard | 60 | 2021-09-01 06:00:00 |
| 3 | 9003 | 算法 | medium | 80 | 2021-09-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 | 2021-09-01 09:01:01 | 2021-09-01 09:31:00 | 81 |
| 2 | 1002 | 9002 | 2021-09-01 12:01:01 | 2021-09-01 12:31:01 | 70 |
| 3 | 1003 | 9001 | 2021-09-01 19:01:01 | 2021-09-01 19:40:01 | 86 |
| 4 | 1003 | 9002 | 2021-09-01 12:01:01 | 2021-09-01 12:31:51 | 89 |
| 5 | 1004 | 9001 | 2021-09-01 19:01:01 | 2021-09-01 19:30:01 | 85 |
+----+------+---------+---------------------+---------------------+-------+
示例数据输出结果：
+------+-----------+
| uid | activity |
+------+-----------+
| 1001 | activity2 |
| 1003 | activity1 |
| 1004 | activity1 |
| 1004 | activity2 |
+------+-----------+
解释：用户1001最小分数81不满足活动1，但29分59秒完成了60分钟长的试卷得分81，满足活动2；
1003最小分数86满足活动1，完成时长都大于试卷时长的一半，不满足活动2；
用户1004刚好用了一半时间（30分钟整）完成了试卷得分85，满足活动1和活动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);
🍌🍌 答案
(select r.uid, 'activity1' as activity
from exam_record r
where year(submit_time) = 2021
group by r.uid
having min(score) >= 85)
union all
(select r.uid,'activity2' as activity
from exam_record r
left join examination_info i on r.exam_id = i.exam_id
where year(submit_time) = 2021 and i.difficulty = 'hard' and score >=80 and
timestampdiff(second,r.start_time,r.submit_time)<= i.duration*30
group by r.uid)
order by uid;
3 🌈 连接查询
🚀 SQL25 满足条件的用户的试卷完成数和题目练习数
📖 user_info表结构
📖 examination_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号 | 3100 | 7 | 算法 | 2020-01-01 10:00:00 |
| 2 | 1002 | 牛客2号 | 2300 | 7 | 算法 | 2020-01-01 10:00:00 |
| 3 | 1003 | 牛客3号 | 2500 | 7 | 算法 | 2020-01-01 10:00:00 |
| 4 | 1004 | 牛客4号 | 1200 | 5 | 算法 | 2020-01-01 10:00:00 |
| 5 | 1005 | 牛客5号 | 1600 | 6 | C++ | 2020-01-01 10:00:00 |
| 6 | 1006 | 牛客6号 | 2000 | 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 | SQL | hard | 60 | 2021-09-01 06:00:00 |
| 2 | 9002 | C++ | hard | 60 | 2021-09-01 06:00:00 |
| 3 | 9003 | 算法 | medium | 80 | 2021-09-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 | 2021-09-01 09:01:01 | 2021-09-01 09:31:00 | 81 |
| 2 | 1002 | 9002 | 2021-09-01 12:01:01 | 2021-09-01 12:31:01 | 81 |
| 3 | 1003 | 9001 | 2021-09-01 19:01:01 | 2021-09-01 19:40:01 | 86 |
| 4 | 1003 | 9002 | 2021-09-01 12:01:01 | 2021-09-01 12:31:51 | 89 |
| 5 | 1004 | 9001 | 2021-09-01 19:01:01 | 2021-09-01 19:30:01 | 85 |
| 6 | 1005 | 9002 | 2021-09-01 12:01:01 | 2021-09-01 12:31:02 | 85 |
| 7 | 1006 | 9003 | 2021-09-07 10:01:01 | 2021-09-07 10:21:01 | 84 |
| 8 | 1006 | 9001 | 2021-09-07 10:01:01 | 2021-09-07 10:21:01 | 80 |
+----+------+---------+---------------------+---------------------+-------+
题目练习记录表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 | 1004 | 8001 | 2021-08-02 19:38:01 | 70 |
| 6 | 1004 | 8002 | 2021-08-02 19:48:01 | 90 |
| 7 | 1001 | 8002 | 2021-08-02 19:38:01 | 70 |
| 8 | 1004 | 8002 | 2021-08-02 19:48:01 | 90 |
| 9 | 1004 | 8002 | 2021-08-02 19:58:01 | 94 |
| 10 | 1004 | 8003 | 2021-08-02 19:38:01 | 70 |
| 11 | 1004 | 8003 | 2021-08-02 19:48:01 | 90 |
| 12 | 1004 | 8003 | 2021-08-01 19:38:01 | 80 |
+----+------+-------------+---------------------+-------+
请你找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬，统计他们的2021年试卷总完成次数和题目总练习次数，只保留2021年有试卷完成记录的用户。
结果按试卷完成数升序，按题目练习数降序。
示例数据输出如下：
+------+----------+--------------+
| uid | exam_cnt | question_cnt |
+------+----------+--------------+
| 1001 | 1 | 2 |
| 1003 | 2 | 0 |
+------+----------+--------------+
解释：用户1001、1003、1004、1006满足高难度SQL试卷得分平均值大于80，但只有1001、1003是7级红名大佬；
1001完成了1次试卷1001，练习了2次题目；
1003完成了2次试卷9001、9002，未练习题目（因此计数为0）
🚀 建表语句
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);
🍌🍌 答案
select er1.uid as uid,
count(distinct er1.exam_id) as exam_cnt,
count(distinct pr1.id) as question_cnt
from exam_record er1
left join practice_record pr1 on er1.uid = pr1.uid and year(er1.submit_time)=2021 and YEAR(pr1.submit_time)=2021
where er1.uid in (
select er.uid
from user_info ui
left join exam_record er on ui.uid = er.uid
join examination_info ei on er.exam_id = ei.exam_id
where ei.tag = 'SQL' and ui.level = 7 and difficulty = 'hard' and year(submit_time)=2021
group by ui.uid
having avg(er.score) >80)
group by er1.uid
order by exam_cnt , question_cnt desc;
🚀 SQL26 每个6/7级用户活跃情况
📖 user_info表结构
📖 examination_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号 | 3100 | 7 | 算法 | 2020-01-01 10:00:00 |
| 2 | 1002 | 牛客2号 | 2300 | 7 | 算法 | 2020-01-01 10:00:00 |
| 3 | 1003 | 牛客3号 | 2500 | 7 | 算法 | 2020-01-01 10:00:00 |
| 4 | 1004 | 牛客4号 | 1200 | 5 | 算法 | 2020-01-01 10:00:00 |
| 5 | 1005 | 牛客5号 | 1600 | 6 | C++ | 2020-01-01 10:00:00 |
| 6 | 1006 | 牛客6号 | 2000 | 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 | SQL | hard | 60 | 2021-09-01 06:00:00 |
| 2 | 9002 | C++ | hard | 60 | 2021-09-01 06:00:00 |
| 3 | 9003 | 算法 | medium | 80 | 2021-09-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 | 2021-09-01 09:01:01 | 2021-09-01 09:31:00 | 81 |
| 2 | 1002 | 9002 | 2021-09-01 12:01:01 | 2021-09-01 12:31:01 | 81 |
| 3 | 1003 | 9001 | 2021-09-01 19:01:01 | 2021-09-01 19:40:01 | 86 |
| 4 | 1003 | 9002 | 2021-09-01 12:01:01 | 2021-09-01 12:31:51 | 89 |
| 5 | 1004 | 9001 | 2021-09-01 19:01:01 | 2021-09-01 19:30:01 | 85 |
| 6 | 1005 | 9002 | 2021-09-01 12:01:01 | 2021-09-01 12:31:02 | 85 |
| 7 | 1006 | 9003 | 2021-09-07 10:01:01 | 2021-09-07 10:21:01 | 84 |
| 8 | 1006 | 9001 | 2021-09-07 10:01:01 | 2021-09-07 10:21:01 | 80 |
+----+------+---------+---------------------+---------------------+-------+
题目练习记录表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 | 1004 | 8001 | 2021-08-02 19:38:01 | 70 |
| 6 | 1004 | 8002 | 2021-08-02 19:48:01 | 90 |
| 7 | 1001 | 8002 | 2021-08-02 19:38:01 | 70 |
| 8 | 1004 | 8002 | 2021-08-02 19:48:01 | 90 |
| 9 | 1004 | 8002 | 2021-08-02 19:58:01 | 94 |
| 10 | 1004 | 8003 | 2021-08-02 19:38:01 | 70 |
| 11 | 1004 | 8003 | 2021-08-02 19:48:01 | 90 |
| 12 | 1004 | 8003 | 2021-08-01 19:38:01 | 80 |
+----+------+-------------+---------------------+-------+
请统计每个6/7级用户总活跃月份数、2021年活跃天数、2021年试卷作答活跃天数、
2021年答题活跃天数，按照总活跃月份数、2021年活跃天数降序排序。由示例数据结果输出如下：
+------+-----------------+---------------+--------------------+------------------------+
| uid | act_month_total | act_days_2021 | act_days_2021_exam | act_days_2021_question |
+------+-----------------+---------------+--------------------+------------------------+
| 1001 | 2 | 2 | 1 | 1 |
| 1002 | 1 | 2 | 1 | 1 |
| 1003 | 1 | 1 | 1 | 0 |
| 1005 | 1 | 1 | 1 | 0 |
| 1006 | 1 | 1 | 1 | 0 |
+------+-----------------+---------------+--------------------+------------------------+
解释：6/7级用户共有5个，其中1006在202109、202108、202008共3个月活跃过，
2021年活跃的日期有20210907、20210804、20210803、20210802共4天，2021年在试卷作答区20210907活跃1天，在题目练习区活跃了3天。
🚀 建表语句
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);
🍌🍌 答案
select t1.uid, count(distinct act_month) act_month_total,
count(distinct case when year(act_day)=2021 then act_day end) act_days_2021,
count(distinct case when left(tag,1)=9 and year(act_day)=2021 then act_day end) act_days_2021_exam,
count(distinct case when left(tag,1)=8 and year(act_day)=2021 then act_day end) act_days_2021_question
from user_info t1
left join (select uid ,
date_format(start_time,'%Y%m') act_month,
date_format(start_time,'%Y%m%d') act_day,
exam_id tag
from exam_record
union all
select uid,
date_format(submit_time,'%Y%m') act_month,
date_format(submit_time,'%Y%m%d') act_day,
question_id tag
from practice_record) t2
on t1.uid=t2.uid
where t1.uid in (select uid
from user_info
where level in (6,7))
group by uid
order by act_month_total desc,act_days_2021 desc;
330
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
