【牛客刷题-SQL进阶挑战】NO3.聚合分组查询

最新推荐文章于 2024-03-18 20:48:16 发布
置顶 最新推荐文章于 2024-03-18 20:48:16 发布
阅读量1.2k 收藏 7
点赞数 7
分类专栏： 如何成为SQL高手 Mysql 文章标签： sql 数据库 dba
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_41645135/article/details/125126144
版权

📢📢📢📣📣📣
哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10年DBA工作经验
一位上进心十足的【大数据领域博主】！😜😜😜
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程
擅长主流数据Oracle、MySQL、PG 运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。
✨ 如果有对【数据库】感兴趣的【小可爱】，欢迎关注【IT邦德】💞💞💞
❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️

文章目录

前言

SQL每个人都要用，但是用来衡量产出的并不是SQL本身，你需要用这个工具，去创造其它的价值。

1 🌈 聚合函数

🚀 SQL14 SQL类别高难度试卷得分的截断平均值

📖 examination_info表结构
在这里插入图片描述
📖 exam_record表结构
在这里插入图片描述

🚀 题目描述
牛客的运营同学想要查看大家在SQL类别中高难度试卷的得分情况。
请你帮她从exam_record数据表中计算所有用户完成SQL类别高难度试卷得分的截断平均值（去掉一个最大值和一个最小值后的平均值）

示例数据：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 | 算法   | 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 | 2020-01-02 09:01:01 | 2020-01-02 09:21:01 |    80 |
|  2 | 1001 |    9001 | 2021-05-02 10:01:01 | 2021-05-02 10:30:01 |    81 |
|  3 | 1001 |    9001 | 2021-06-02 19:01:01 | 2021-06-02 19:31:01 |    84 |
|  4 | 1001 |    9002 | 2021-09-05 19:01:01 | 2021-09-05 19:40:01 |    89 |
|  5 | 1001 |    9001 | 2021-09-02 12:01:01 | NULL                |  NULL |
|  6 | 1001 |    9002 | 2021-09-01 12:01:01 | NULL                |  NULL |
|  7 | 1002 |    9002 | 2021-02-02 19:01:01 | 2021-02-02 19:30:01 |    87 |
|  8 | 1002 |    9001 | 2021-05-05 18:01:01 | 2021-05-05 18:59:02 |    90 |
|  9 | 1003 |    9001 | 2021-02-06 12:01:01 | NULL                |  NULL |
| 10 | 1003 |    9001 | 2021-09-07 10:01:01 | 2021-09-07 10:31:01 |    50 |
+----+------+---------+---------------------+---------------------+-------+

从examination_info表可知，试卷9001为高难度SQL试卷，该试卷被作答的得分有[80,81,84,90,50]，
去除最高分和最低分后为[80,81,84]，平均分为81.6666667，保留一位小数后为81.7

根据输入你的查询结果如下：
+------+------------+-----------+
| tag  | difficulty | avg_score |
+------+------------+-----------+
| SQL  | hard       |      81.7 |
+------+------------+-----------+

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

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

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

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:01', 80),
(1001, 9001, '2021-05-02 10:01:01', '2021-05-02 10:30:01', 81),
(1001, 9001, '2021-06-02 19:01:01', '2021-06-02 19:31:01', 84),
(1001, 9002, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89),
(1001, 9001, '2021-09-02 12:01:01', null, null),
(1001, 9002, '2021-09-01 12:01:01', null, null),
(1002, 9002, '2021-02-02 19:01:01', '2021-02-02 19:30:01', 87),
(1002, 9001, '2021-05-05 18:01:01', '2021-05-05 18:59:02', 90),
(1003, 9001, '2021-02-06 12:01:01', null, null),
(1003, 9001, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 50);

🍌🍌 答案
select tag,difficulty,
round((sum(score)-min(score)-max(score))/
      (count(score)-2),1) as avg_score
from examination_info 
join exam_record using(exam_id)
where tag='SQL' and difficulty='hard';

在这里插入图片描述

🚀 SQL15 统计作答次数

📖 exam_record表结构
在这里插入图片描述

🚀 题目描述
有一个试卷作答记录表exam_record，请从中统计出总作答次数total_pv、试卷已完成作答数complete_pv、已完成的试卷数complete_exam_cnt。
示例数据 exam_record表（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：
+----+------+---------+---------------------+---------------------+-------+
| id | uid  | exam_id | start_time          | submit_time         | score |
+----+------+---------+---------------------+---------------------+-------+
|  1 | 1001 |    9001 | 2020-01-02 09:01:01 | 2020-01-02 09:21:01 |    80 |
|  2 | 1001 |    9001 | 2021-05-02 10:01:01 | 2021-05-02 10:30:01 |    81 |
|  3 | 1001 |    9001 | 2021-06-02 19:01:01 | 2021-06-02 19:31:01 |    84 |
|  4 | 1001 |    9002 | 2021-09-05 19:01:01 | 2021-09-05 19:40:01 |    89 |
|  5 | 1001 |    9001 | 2021-09-02 12:01:01 | NULL                |  NULL |
|  6 | 1001 |    9002 | 2021-09-01 12:01:01 | NULL                |  NULL |
|  7 | 1002 |    9002 | 2021-02-02 19:01:01 | 2021-02-02 19:30:01 |    87 |
|  8 | 1002 |    9001 | 2021-05-05 18:01:01 | 2021-05-05 18:59:02 |    90 |
|  9 | 1003 |    9001 | 2021-02-06 12:01:01 | NULL                |  NULL |
| 10 | 1003 |    9001 | 2021-09-07 10:01:01 | 2021-09-07 10:31:01 |    89 |
| 11 | 1004 |    9001 | 2021-09-06 12:01:01 | NULL                |  NULL |
+----+------+---------+---------------------+---------------------+-------+

示例输出：
+----------+--------------+-------------------+
| total_pv | compelete_pv | complete_exam_cnt |
+----------+--------------+-------------------+
|       11 |            7 |                 2 |
+----------+--------------+-------------------+
解释：表示截止当前，有11次试卷作答记录，已完成的作答次数为7次（中途退出的为未完成状态，其交卷时间和份数为NULL），
已完成的试卷有9001和9002两份。


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

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:01', 80),
(1001, 9001, '2021-05-02 10:01:01', '2021-05-02 10:30:01', 81),
(1001, 9001, '2021-06-02 19:01:01', '2021-06-02 19:31:01', 84),
(1001, 9002, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89),
(1001, 9001, '2021-09-02 12:01:01', null, null),
(1001, 9002, '2021-09-01 12:01:01', null, null),
(1002, 9002, '2021-02-02 19:01:01', '2021-02-02 19:30:01', 87),
(1002, 9001, '2021-05-05 18:01:01', '2021-05-05 18:59:02', 90),
(1003, 9001, '2021-02-06 12:01:01', null, null),
(1003, 9001, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 89),
(1004, 9001, '2021-09-06 12:01:01', null, null);

🍌🍌 答案
select 
DISTINCT count(*) as total_pv,
count(submit_time) as compelete_pv,
count(distinct exam_id and score IS not NULL) as complete_exam_cnt
from exam_record;

在这里插入图片描述

🚀 SQL16 得分不小于平均分的最低分

📖 examination_info表结构
在这里插入图片描述
📖 exam_record表结构
在这里插入图片描述

🚀 题目描述
请从试卷作答记录表中找到SQL试卷得分不小于该类试卷平均得分的用户最低得分。
示例数据 exam_record表（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：
+----+------+---------+---------------------+---------------------+-------+
| id | uid  | exam_id | start_time          | submit_time         | score |
+----+------+---------+---------------------+---------------------+-------+
|  1 | 1001 |    9001 | 2020-01-02 09:01:01 | 2020-01-02 09:21:01 |    80 |
|  2 | 1002 |    9001 | 2021-09-05 19:01:01 | 2021-09-05 19:40:01 |    89 |
|  3 | 1002 |    9002 | 2021-09-02 12:01:01 | NULL                |  NULL |
|  4 | 1002 |    9003 | 2021-09-01 12:01:01 | NULL                |  NULL |
|  5 | 1002 |    9001 | 2021-02-02 19:01:01 | 2021-02-02 19:30:01 |    87 |
|  6 | 1002 |    9002 | 2021-05-05 18:01:01 | 2021-05-05 18:59:02 |    90 |
|  7 | 1003 |    9002 | 2021-02-06 12:01:01 | NULL                |  NULL |
|  8 | 1003 |    9003 | 2021-09-07 10:01:01 | 2021-09-07 10:31:01 |    86 |
|  9 | 1004 |    9003 | 2021-09-06 12:01:01 | NULL                |  NULL |
+----+------+---------+---------------------+---------------------+-------+

examination_info表（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）
+----+---------+--------+------------+----------+---------------------+
| id | exam_id | tag    | difficulty | duration | release_time        |
+----+---------+--------+------------+----------+---------------------+
|  1 |    9001 | SQL    | hard       |       60 | 2020-01-01 10:00:00 |
|  2 |    9002 | SQL    | easy       |       60 | 2020-02-01 10:00:00 |
|  3 |    9003 | 算法   | medium     |       80 | 2020-08-02 10:00:00 |
+----+---------+--------+------------+----------+---------------------+

示例输出数据：
+--------------------+
| min_score_over_avg |
+--------------------+
|                 87 |
+--------------------+

保证至少有一个有效的SQL类别的试卷作答分数
解释：试卷9001和9002为SQL类别，作答这两份试卷的得分有[80,89,87,90]，平均分为86.5，不小于平均分的最小分数为87

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

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

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

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:01', 80),
(1002, 9001, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89),
(1002, 9002, '2021-09-02 12:01:01', null, null),
(1002, 9003, '2021-09-01 12:01:01', null, null),
(1002, 9001, '2021-02-02 19:01:01', '2021-02-02 19:30:01', 87),
(1002, 9002, '2021-05-05 18:01:01', '2021-05-05 18:59:02', 90),
(1003, 9002, '2021-02-06 12:01:01', null, null),
(1003, 9003, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 86),
(1004, 9003, '2021-09-06 12:01:01', null, null);

🍌🍌 答案
select min(score) as min_score_over_avg
from examination_info as i 
join exam_record as r
on i.exam_id = r.exam_id
where tag = 'SQL'
and score >= (select avg(score)
              from examination_info as i 
              join exam_record as r
              on i.exam_id = r.exam_id
              where tag = 'SQL');

在这里插入图片描述

2 🌈 分组查询

在这里插入图片描述

🚀 SQL17 平均活跃天数和月活人数

📖 exam_record表结构
在这里插入图片描述

🚀 题目描述
用户在牛客试卷作答区作答记录存储在表exam_record中，内容如下：
exam_record表（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）
+----+------+---------+---------------------+---------------------+-------+
| id | uid  | exam_id | start_time          | submit_time         | score |
+----+------+---------+---------------------+---------------------+-------+
|  1 | 1001 |    9001 | 2021-07-02 09:01:01 | 2021-07-02 09:21:01 |    80 |
|  2 | 1002 |    9001 | 2021-09-05 19:01:01 | 2021-09-05 19:40:01 |    81 |
|  3 | 1002 |    9002 | 2021-09-02 12:01:01 | NULL                |  NULL |
|  4 | 1002 |    9003 | 2021-09-01 12:01:01 | NULL                |  NULL |
|  5 | 1002 |    9001 | 2021-07-02 19:01:01 | 2021-07-02 19:30:01 |    82 |
|  6 | 1002 |    9002 | 2021-07-05 18:01:01 | 2021-07-05 18:59:02 |    90 |
|  7 | 1003 |    9002 | 2021-07-06 12:01:01 | NULL                |  NULL |
|  8 | 1003 |    9003 | 2021-09-07 10:01:01 | 2021-09-07 10:31:01 |    86 |
|  9 | 1004 |    9003 | 2021-09-06 12:01:01 | NULL                |  NULL |
| 10 | 1002 |    9003 | 2021-09-01 12:01:01 | 2021-09-01 12:31:01 |    81 |
| 11 | 1005 |    9001 | 2021-09-01 12:01:01 | 2021-09-01 12:31:01 |    88 |
| 12 | 1006 |    9002 | 2021-09-02 12:11:01 | 2021-09-02 12:31:01 |    89 |
| 13 | 1007 |    9002 | 2020-09-02 12:11:01 | 2020-09-02 12:31:01 |    89 |
+----+------+---------+---------------------+---------------------+-------+

请计算2021年每个月里试卷作答区用户平均月活跃天数avg_active_days和月度活跃人数mau，上面数据的示例输出如下：
+--------+-----------------+-----+
| month  | avg_active_days | mau |
+--------+-----------------+-----+
| 202107 |            1.50 |   2 |
| 202109 |            1.25 |   4 |
+--------+-----------------+-----+

解释：2021年7月有2人活跃，共活跃了3天（1001活跃1天，1002活跃2天），平均活跃天数1.5；
2021年9月有4人活跃，共活跃了5天，平均活跃天数1.25，结果保留2位小数。
注：此处活跃指有交卷行为。


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

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2021-07-02 09:01:01', '2021-07-02 09:21:01', 80),
(1002, 9001, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 81),
(1002, 9002, '2021-09-02 12:01:01', null, null),
(1002, 9003, '2021-09-01 12:01:01', null, null),
(1002, 9001, '2021-07-02 19:01:01', '2021-07-02 19:30:01', 82),
(1002, 9002, '2021-07-05 18:01:01', '2021-07-05 18:59:02', 90),
(1003, 9002, '2021-07-06 12:01:01', null, null),
(1003, 9003, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 86),
(1004, 9003, '2021-09-06 12:01:01', null, null),
(1002, 9003, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 81),
(1005, 9001, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 88),
(1006, 9002, '2021-09-02 12:11:01', '2021-09-02 12:31:01', 89),
(1007, 9002, '2020-09-02 12:11:01', '2020-09-02 12:31:01', 89);

🍌🍌 答案
select concat(substr(submit_time,1,4),substr(submit_time,6,2)) as month
, round(count(distinct uid, day(submit_time))/count(distinct uid),2) as avg_active_days
, round(count(distinct(uid)),0) as mau
from exam_record
where submit_time is not null
and year(submit_time) ='2021'
group by month;

在这里插入图片描述

🚀 SQL18 月总刷题数和日均刷题数

📖 practice_record表结构
在这里插入图片描述

🚀 题目描述
现有一张题目练习记录表practice_record，示例内容如下：
+----+------+-------------+---------------------+-------+
| id | uid  | question_id | submit_time         | score |
+----+------+-------------+---------------------+-------+
|  1 | 1001 |        8001 | 2021-08-02 11:41:01 |    60 |
|  2 | 1002 |        8001 | 2021-09-02 19:30:01 |    50 |
|  3 | 1002 |        8001 | 2021-09-02 19:20:01 |    70 |
|  4 | 1002 |        8002 | 2021-09-02 19:38:01 |    70 |
|  5 | 1003 |        8002 | 2021-08-01 19:38:01 |    80 |
+----+------+-------------+---------------------+-------+


请从中统计出2021年每个月里用户的月总刷题数month_q_cnt 和日均刷题数avg_day_q_cnt（按月份升序排序）以及该年的总体情况，示例数据输出如下：
+--------------+-------------+---------------+
| submit_month | month_q_cnt | avg_day_q_cnt |
+--------------+-------------+---------------+
| 202108       |           2 |         0.065 |
| 202109       |           3 |         0.100 |
| 2021汇总     |           5 |         0.161 |
+--------------+-------------+---------------+

解释：2021年8月共有2次刷题记录，日均刷题数为2/31=0.065（保留3位小数）；
2021年9月共有3次刷题记录，日均刷题数为3/30=0.100；
2021年共有5次刷题记录（年度汇总平均无实际意义，这里我们按照31天来算5/31=0.161）



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

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


🍌🍌 答案
SELECT IFNULL(sm, "2021汇总") submit_month
       , COUNT(question_id) month_q_cnt
       , ROUND(COUNT(question_id) / MAX(DAY(LAST_DAY(submit_time))),3) avg_day_q_cnt
FROM (SELECT *, DATE_FORMAT(submit_time, "%Y%m") sm FROM practice_record) t1
WHERE YEAR(submit_time) = 2021
GROUP BY sm WITH ROLLUP
ORDER BY submit_month;

在这里插入图片描述

🚀 SQL19 未完成试卷数大于1的有效用户

📖 examination_info表结构
在这里插入图片描述
📖 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-07-02 09:01:01 | 2021-07-02 09:21:01 |    80 |
|  2 | 1002 |    9001 | 2021-09-05 19:01:01 | 2021-09-05 19:40:01 |    81 |
|  3 | 1002 |    9002 | 2021-09-02 12:01:01 | NULL                |  NULL |
|  4 | 1002 |    9003 | 2021-09-01 12:01:01 | NULL                |  NULL |
|  5 | 1002 |    9001 | 2021-07-02 19:01:01 | 2021-07-02 19:30:01 |    82 |
|  6 | 1002 |    9002 | 2021-07-05 18:01:01 | 2021-07-05 18:59:02 |    90 |
|  7 | 1003 |    9002 | 2021-07-06 12:01:01 | NULL                |  NULL |
|  8 | 1003 |    9003 | 2021-09-07 10:01:01 | 2021-09-07 10:31:01 |    86 |
|  9 | 1004 |    9003 | 2021-09-06 12:01:01 | NULL                |  NULL |
| 10 | 1002 |    9003 | 2021-09-01 12:01:01 | 2021-09-01 12:31:01 |    81 |
| 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 | 1006 |    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 | SQL    | easy       |       60 | 2020-02-01 10:00:00 |
|  3 |    9003 | 算法   | medium     |       80 | 2020-08-02 10:00:00 |
+----+---------+--------+------------+----------+---------------------+


请统计2021年每个未完成试卷作答数大于1的有效用户的数据（有效用户指完成试卷作答数至少为1且未完成数小于5），
输出用户ID、未完成试卷作答数、完成试卷作答数、作答过的试卷tag集合，按未完成试卷数量由多到少排序。示例数据的输出结果如下：
+------+----------------+--------------+-------------------------------------------------------------------------------+
| uid  | incomplete_cnt | complete_cnt | detail                                                                        |
+------+----------------+--------------+-------------------------------------------------------------------------------+
| 1002 |              2 |            4 | 2021-07-02:SQL;2021-07-05:SQL;2021-09-01:算法;2021-09-02:SQL;2021-09-05:SQL   |
+------+----------------+--------------+-------------------------------------------------------------------------------+

解释：2021年的作答记录中，除了1004，其他用户均满足有效用户定义，但只有1002未完成试卷数大于1，
因此只输出1002，detail中是1002作答过的试卷{日期:tag}集合，日期和tag间用:连接，多元素间用;连接。


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

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

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

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2021-07-02 09:01:01', '2021-07-02 09:21:01', 80),
(1002, 9001, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 81),
(1002, 9002, '2021-09-02 12:01:01', null, null),
(1002, 9003, '2021-09-01 12:01:01', null, null),
(1002, 9001, '2021-07-02 19:01:01', '2021-07-02 19:30:01', 82),
(1002, 9002, '2021-07-05 18:01:01', '2021-07-05 18:59:02', 90),
(1003, 9002, '2021-07-06 12:01:01', null, null),
(1003, 9003, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 86),
(1004, 9003, '2021-09-06 12:01:01', null, null),
(1002, 9003, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 81),
(1005, 9001, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 88),
(1005, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 88),
(1006, 9002, '2021-09-02 12:11:01', '2021-09-02 12:31:01', 89);

🍌🍌 答案
SELECT uid, count(incomplete) as incomplete_cnt,
    count(complete) as complete_cnt,
    group_concat(distinct concat_ws(':', date(start_time), tag) SEPARATOR ';') as detail
from (
    SELECT uid, tag, start_time,
        if(submit_time is null, 1, null) as incomplete,
        if(submit_time is null, null, 1) as complete
    from exam_record 
    left join examination_info using(exam_id)
    where year(start_time)=2021
) as exam_complete_rec
group by uid
having complete_cnt >= 1 and incomplete_cnt BETWEEN 2 and 4
order by incomplete_cnt DESC;

在这里插入图片描述
在这里插入图片描述

IT邦德
关注
  • 7
    点赞
  • 踩
  • 7
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 5
    评论
专栏目录
牛客刷题-SQL进阶挑战大总结（细节狂魔）
IT邦德
06-13 6317
SQL每个人都要用，但是用来衡量产出的并不是SQL本身，你需要用这个工具，去创造其它的价值。
mysql牛客刷题（SQL进阶挑战）
ljm_99的博客
04-11 3454
牛客网sql进阶挑战题目整理
【牛客刷题-SQL进阶挑战】NO5.窗口函数
IT邦德
06-04 1677
SQL每个人都要用，但是用来衡量产出的并不是SQL本身，你需要用这个工具，去创造其它的价值。
【MySQL】【牛客-SQL进阶挑战】03 聚合分组查询
guliguliguliguli的博客
08-14 308
MySQL 聚合函数的使用，有一定的难度
【牛客刷题-SQL进阶挑战】NO1.增删改操作
IT邦德
06-04 2700
SQL每个人都要用，但是用来衡量产出的并不是SQL本身，你需要用这个工具，去创造其它的价值。
【牛客刷题-SQL进阶挑战】NO2.表与索引操作
IT邦德
06-04 1316
SQL每个人都要用，但是用来衡量产出的并不是SQL本身，你需要用这个工具，去创造其它的价值。
【牛客刷题-SQL进阶挑战】NO6.其他常用操作
IT邦德
06-05 1467
SQL每个人都要用，但是用来衡量产出的并不是SQL本身，你需要用这个工具，去创造其它的价值。
牛客题霸-SQL进阶篇（刷题记录一）
大面鱼的博客
03-18 1110
本文基于前段时间学习总结的 MySQL 相关的查询语法，在牛客网找了相应的 MySQL 题目进行练习，以便加强对于 MySQL 查询语法的理解和应用。由于涉及到的数据库表较多，因此本文不再展示，只提供 MySQL 代码与示例输出。部分题目因为较难，难以独立做出或代码仅在平台上运行不成功，故附上题目解法讨论的链接供大家参考。由于部分题目中涉及的内容并不是未来实习和工作中的主要方向，因此本文暂时会跳过一部分题目。
【牛客刷题-SQL】SQL1 查询所有列
Redamancy06的博客
08-15 2万+
🤵‍♂️ 个人主页:📒 系列专栏：《在线编程-SQL篇》🌐推荐一款找工作神器网站：《》|笔试题库|面试经验|实习招聘内推|🐋 希望大家多多支持😘一起进步呀！
牛客SQL刷题 Day-07
12-14
目录 1、获取select * from employees对应的执行计划 2、将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分 3、创建一个actor表，包含如下列信息 4、对于表actor批量插入如下...
牛客刷题总结.docx
01-28
牛客刷题总结.docx
C++面试--牛客(1).pdf
最新发布
03-21
C++面试--牛客(1).pdf
牛客SQL题解 – 从titles表获取按照title进行分组
12-14
从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。 CREATE TABLE IF NOT EXISTS “titles” ( `emp_no` int(11) NOT NULL, `title` varchar(50) NOT NULL, `from_date` date NOT ...
剑指offer（java版）----牛客.zip
09-20
剑指offer-牛客-Java版本 。
MySQL八股文连环45问,你能坚持第几问？
热门推荐
IT邦德
04-05 6万+
文人从事多年面试工作，将MySQL面试分享给大家，希望大家顺利拿下offer
MySQL夺命16问，你能坚持到第几问？
IT邦德
03-23 3万+
MySQL在面试中经常被问到，本文总结了面试中的经典问题。
【MySQL8入门到精通】体系架构-日志文件
IT邦德
12-23 2万+
工作中要用到MySQL,面试被问到MySQL, 此篇文档讲MySQL的文件结构做了详细的阐述
教你找回MySQL管理员root密码的3个妙招
IT邦德
11-01 1万+
教你找回MySQL管理员root密码的3个妙招
牛客刷题 jsnode 输入输出
08-25
在牛客网上刷题，使用 JavaScript 和 Node.js 进行输入输出操作是非常常见的。下面是一个简单的示例，演示如何使用 Node.js 读取标准输入并输出结果： ```javascript // 导入内置的 `readline` 模块 const readline...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 5
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

IT邦德

客户部署资料，步骤超详细

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值