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

irpas 02-07 4482

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

文章目录前言1 🌈 聚合函数🚀 SQL14 SQL类别高难度试卷得分的截断平均值🚀 SQL15 统计作答次数🚀 SQL16 得分不小于平均分的最低分 2 🌈 分组查询🚀 SQL17 平均活跃天数和月活人数🚀 SQL18 月总刷题数和日均刷题数🚀 SQL19 未完成试卷数大于1的有效用户

前言 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;

1.本站遵循行业规范，任何转载的稿件都会明确标注作者和来源；2.本站的原创文章，会注明原创字样,如未注明都非原创，如有侵权请联系删除！；3.作者投稿可能会经我们编辑修改或补充；4.本站不提供任何储存功能只提供收集或者投稿人的网盘链接。