📢📢📢📣📣📣
哈喽！大家好，我是【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_scorefrom 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_cntfrom 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_avgfrom examination_info as i join exam_record as ron i.exam_id = r.exam_idwhere tag = 'SQL'and score >= (select avg(score)from examination_info as i join exam_record as ron i.exam_id = r.exam_idwhere 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 maufrom exam_recordwhere submit_time is not nulland 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_cntFROM (SELECT *, DATE_FORMAT(submit_time, "%Y%m") sm FROM practice_record) t1WHERE YEAR(submit_time) = 2021GROUP BY sm WITH ROLLUPORDER 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 detailfrom (    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_recgroup by uidhaving complete_cnt >= 1 and incomplete_cnt BETWEEN 2 and 4order by incomplete_cnt DESC;