【牛客刷题-SQL进阶挑战】NO4.多表查询
📢📢📢📣📣📣
哈喽!大家好,我是【IT邦德】,江湖人称jeames007,10年DBA工作经验
一位上进心十足的【大数据领域博主】!😜😜😜
中国DBA联盟(ACDU)成员,目前从事DBA及程序编程
擅长主流数据Oracle、MySQL、PG 运维开发,备份恢复,安装迁移,性能优化、故障应急处理等。
✨ 如果有对【数据库】感兴趣的【小可爱】,欢迎关注【IT邦德】💞💞💞
❤️❤️❤️感谢各位大可爱小可爱!❤️❤️❤️
文章目录
- 前言
-
-
- 1 🌈 嵌套子查询
-
- 🚀 SQL20 月均完成试卷数不小于3的用户爱作答的类别
- 🚀 SQL21 试卷发布当天作答人数和平均分
- 🚀 SQL22 作答试卷得分大于过80的人的用户等级分布
- 2 🌈 合并查询
-
- 🚀 SQL23 每个题目和每份试卷被作答的人数和次数
- 🚀 SQL24 分别满足两个活动的人
- 3 🌈 连接查询
-
- 🚀 SQL25 满足条件的用户的试卷完成数和题目练习数
- 🚀 SQL26 每个6/7级用户活跃情况
-
前言
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_cntfrom exam_record er inner join examination_info eion er.exam_id = ei.exam_idwhere uid in (select uidfrom exam_record er group by uid, month(start_time)having count(submit_time) >= 3)group by tagorder 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_scoreFROM exam_recordWHERE (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_idORDER 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_cntFROM exam_record er JOIN user_info ui USING(uid)JOIN examination_info ei USING(exam_id)WHERE ei.tag='SQL' AND er.score > 80GROUP BY levelORDER 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 pvfrom exam_recordgroup by exam_idunion ALLSELECT question_id as tid, count(distinct uid) as uv, count(uid) as pvfrom practice_recordgroup by question_idorder 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 activityfrom exam_record rwhere year(submit_time) = 2021 group by r.uidhaving min(score) >= 85)union all(select r.uid,'activity2' as activity from exam_record rleft join examination_info i on r.exam_id = i.exam_idwhere year(submit_time) = 2021 and i.difficulty = 'hard' and score >=80 and timestampdiff(second,r.start_time,r.submit_time)<= i.duration*30group 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_cntfrom exam_record er1left join practice_record pr1 on er1.uid = pr1.uid and year(er1.submit_time)=2021 and YEAR(pr1.submit_time)=2021where er1.uid in ( select er.uidfrom user_info uileft join exam_record er on ui.uid = er.uidjoin examination_info ei on er.exam_id = ei.exam_idwhere ei.tag = 'SQL' and ui.level = 7 and difficulty = 'hard' and year(submit_time)=2021group by ui.uidhaving avg(er.score) >80)group by er1.uidorder 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_questionfrom user_info t1left join (select uid , date_format(start_time,'%Y%m') act_month, date_format(start_time,'%Y%m%d') act_day, exam_id tagfrom exam_recordunion allselect uid, date_format(submit_time,'%Y%m') act_month, date_format(submit_time,'%Y%m%d') act_day, question_id tagfrom practice_record) t2on t1.uid=t2.uidwhere t1.uid in (select uid from user_infowhere level in (6,7))group by uidorder by act_month_total desc,act_days_2021 desc;