SQL128 未完成试卷数大于1的有效用户
携手创作,共同成长!这是我参与「掘金日新计划 · 8 月更文挑战」的第13天,点击查看活动详情
1、题目📑
现有试卷作答记录表exam_record(uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分),示例数据如下:
示例:exam_record
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-09-01:算法;2021-07-02:SQL;2021-09-02:SQL;2021-09-05:SQL;2021-07-05:SQL |
解释:2021年的作答记录中,除了1004,其他用户均满足有效用户定义,但只有1002未完成试卷数大于1,因此只输出1002,detail中是1002作答过的试卷{日期:tag}集合,日期和tag间用:连接,多元素间用;连接。
示例1
输入:
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);
输出:
1002|2|4|2021-07-02:SQL;2021-07-05:SQL;2021-09-01:算法;2021-09-02:SQL;2021-09-05:SQL
2、思路🧠
细节剖析:
-
统计作答过的tag集合:
- 对于每条作答tag,用:连接日期和tag:
concat_ws(':', date(start_time), tag)
- 对于一个人(组内)的多条作答,用
;
连接去重后的作答记录:
group_concat(distinct concat_ws(':', date(start_time), tag) SEPARATOR ';')
- 对于每条作答tag,用:连接日期和tag:
-
有效用户,指完成试卷作答数至少为
1
且未完成数小于5
-
未完成试卷作答数大于1
-
结合得
1<未完成试卷答题数<5
,完成试卷作答数 >= 1
-
考试开始年限为
2021
年
细节问题:
-
表头重命名:as
-
该题不能使用score去判断是否为null,在提交的时候,测试用例某人有成绩没提交时间。所以只能使用提交时间去进行为null的判断。
SELECT
uid,
SUM(
IF
( score IS NULL, 1, 0 )) AS incomplete_cnt,
SUM(
IF
( score IS NULL, 0, 1 )) AS complete_cnt,
group_concat( DISTINCT concat_ws( ':', date( start_time ), tag ) ORDER BY start_time SEPARATOR ';' ) AS detail
FROM
exam_record er
INNER JOIN examination_info ei ON er.exam_id = ei.exam_id
WHERE
YEAR ( start_time ) = 2021
GROUP BY
uid
HAVING
complete_cnt >= 1
AND incomplete_cnt > 1
AND incomplete_cnt < 5
ORDER BY
incomplete_cnt DESC
3、代码👨💻
commit AC
SELECT
uid,
SUM(
IF
( submit_time IS NULL, 1, 0 )) AS incomplete_cnt,
SUM(
IF
( submit_time IS NULL, 0, 1 )) AS complete_cnt,
group_concat( DISTINCT concat_ws( ':', date( start_time ), tag ) ORDER BY start_time SEPARATOR ';' ) AS detail
FROM
exam_record er
INNER JOIN examination_info ei ON er.exam_id = ei.exam_id
WHERE
YEAR ( start_time ) = 2021
GROUP BY
uid
HAVING
complete_cnt >= 1
AND incomplete_cnt > 1
AND incomplete_cnt < 5
ORDER BY
incomplete_cnt DESC
4、总结
该题目的对SQL的语法及基础知识,学会使用 CONCAT_WS()
、GROUP_CONCAT()
函数的使用,要合理选择解决办法,像内连接、外连接、左连接、右连接等都要有相关的了解,其次当你编写了大量的SQL之后,就要学会进行SQL的优化,这对于数据查询的时间会有大幅度的降低。
函数详解:
MySQL group_concat()函数 - MySQL教程 (yiibai.com)
SQL CONCAT_WS()函数 - SQL教程 (yiibai.com)
❤️来自专栏《Mysql每日一题》欢迎订阅❤️
厂长写博客目的初衷很简单,希望大家在学习的过程中少走弯路,多学一些东西,对自己有帮助的留下你的赞赞👍或者关注➕都是对我最大的支持,你的关注和点赞给厂长每天更文的动力。
对文章其中一部分不理解,都可以评论区回复我,我们来一起讨论,共同学习,一起进步!