> 文档中心 > 【牛客刷题-SQL进阶挑战】NO4.多表查询

【牛客刷题-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本身,你需要用这个工具,去创造其它的价值。


【牛客刷题-SQL进阶挑战】NO4.多表查询

1 🌈 嵌套子查询

🚀 SQL20 月均完成试卷数不小于3的用户爱作答的类别

📖 examination_info表结构
【牛客刷题-SQL进阶挑战】NO4.多表查询
📖 exam_record表结构
【牛客刷题-SQL进阶挑战】NO4.多表查询

🚀 题目描述现有试卷作答记录表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的用户们爱作答的类别及作答次数,按次数降序输出,示例输出如下:解释:用户10021005202109月的完成试卷数目均为3,其他用户均小于3;然后用户10021005作答过的试卷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;

【牛客刷题-SQL进阶挑战】NO4.多表查询

🚀 SQL21 试卷发布当天作答人数和平均分

📖 user_info表结构
【牛客刷题-SQL进阶挑战】NO4.多表查询
📖 examination_info表结构
【牛客刷题-SQL进阶挑战】NO4.多表查询
📖 exam_record表结构
【牛客刷题-SQL进阶挑战】NO4.多表查询

🚀 题目描述现有用户信息表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)有1001100210031005作答过,但是10035级用户,其他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降序,相同人数的按平均分升序

【牛客刷题-SQL进阶挑战】NO4.多表查询

🚀 SQL22 作答试卷得分大于过80的人的用户等级分布

📖 user_info表结构
【牛客刷题-SQL进阶挑战】NO4.多表查询
📖 examination_info表结构
【牛客刷题-SQL进阶挑战】NO4.多表查询
📖 exam_record表结构
【牛客刷题-SQL进阶挑战】NO4.多表查询

🚀 题目描述现有用户信息表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的人的用户等级分布,按数量降序排序(保证数量都不同)。示例数据结果输出如下:解释:9001SQL类试卷,作答该试卷大于80分的人有1002100310053人,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;

【牛客刷题-SQL进阶挑战】NO4.多表查询

2 🌈 合并查询

🚀 SQL23 每个题目和每份试卷被作答的人数和次数

📖 exam_record表结构
【牛客刷题-SQL进阶挑战】NO4.多表查询
📖 practice_record表结构
【牛客刷题-SQL进阶挑战】NO4.多表查询

🚀 题目描述现有试卷作答记录表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次试卷90011人作答39002;“刷题”有3人刷58001,有2人刷28002🚀 建表语句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;

【牛客刷题-SQL进阶挑战】NO4.多表查询

🚀 SQL24 分别满足两个活动的人

📖 examination_info表结构
【牛客刷题-SQL进阶挑战】NO4.多表查询
📖 exam_record表结构
【牛客刷题-SQL进阶挑战】NO4.多表查询

🚀 题目描述为了促进更多用户在牛客平台学习和刷题进步,我们会经常给一些既活跃又表现不错的用户发放福利。假使以前我们有两拨运营活动,分别给每次试卷得分都能到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,但2959秒完成了60分钟长的试卷得分81,满足活动21003最小分数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;

【牛客刷题-SQL进阶挑战】NO4.多表查询

3 🌈 连接查询

🚀 SQL25 满足条件的用户的试卷完成数和题目练习数

📖 user_info表结构
【牛客刷题-SQL进阶挑战】NO4.多表查询
📖 examination_info表结构【牛客刷题-SQL进阶挑战】NO4.多表查询
📖 exam_record表结构
【牛客刷题-SQL进阶挑战】NO4.多表查询
📖 practice_record表结构
【牛客刷题-SQL进阶挑战】NO4.多表查询

🚀 题目描述现有用户信息表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 |+------+----------+--------------+解释:用户1001100310041006满足高难度SQL试卷得分平均值大于80,但只有100110037级红名大佬;1001完成了1次试卷1001,练习了2次题目;1003完成了2次试卷90019002,未练习题目(因此计数为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;

【牛客刷题-SQL进阶挑战】NO4.多表查询

🚀 SQL26 每个6/7级用户活跃情况

📖 user_info表结构
【牛客刷题-SQL进阶挑战】NO4.多表查询
📖 examination_info表结构【牛客刷题-SQL进阶挑战】NO4.多表查询
📖 exam_record表结构
【牛客刷题-SQL进阶挑战】NO4.多表查询
📖 practice_record表结构
【牛客刷题-SQL进阶挑战】NO4.多表查询

🚀 题目描述现有用户信息表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个,其中10062021092021082020083个月活跃过,2021年活跃的日期有202109072021080420210803202108024天,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;

【牛客刷题-SQL进阶挑战】NO4.多表查询

在这里插入图片描述

四四频道