0
0

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

📢📢📢📣📣📣
哈喽！大家好，我是【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-0110:00:00||2|9002| 算法   | medium     |80|2020-08-0210: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-0209:01:01|2020-01-0209:21:01|80||2|1001|9001|2021-05-0210:01:01|2021-05-0210:30:01|81||3|1001|9001|2021-06-0219:01:01|2021-06-0219:31:01|84||4|1001|9002|2021-09-0519:01:01|2021-09-0519:40:01|89||5|1001|9001|2021-09-0212:01:01|NULL|NULL||6|1001|9002|2021-09-0112:01:01|NULL|NULL||7|1002|9002|2021-02-0219:01:01|2021-02-0219:30:01|87||8|1002|9001|2021-05-0518:01:01|2021-05-0518:59:02|90||9|1003|9001|2021-02-0612:01:01|NULL|NULL||10|1003|9001|2021-09-0710:01:01|2021-09-0710: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|+------+------------+-----------+

🚀 建表语句
droptableifexists examination_info;CREATETABLE  examination_info (
    id intPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    exam_id intUNIQUENOTNULLCOMMENT'试卷ID',
    tag varchar(32)COMMENT'类别标签',
    difficulty varchar(8)COMMENT'难度',
    duration intNOTNULLCOMMENT'时长',
    release_time datetimeCOMMENT'发布时间')CHARACTERSET utf8 COLLATE utf8_general_ci;droptableifexists exam_record;CREATETABLE exam_record (
    id intPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    uid intNOTNULLCOMMENT'用户ID',
    exam_id intNOTNULLCOMMENT'试卷ID',
    start_time datetimeNOTNULLCOMMENT'开始时间',
    submit_time datetimeCOMMENT'提交时间',
    score tinyintCOMMENT'得分')CHARACTERSET utf8 COLLATE utf8_general_ci;INSERTINTO 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');INSERTINTO 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-0209:01:01|2020-01-0209:21:01|80||2|1001|9001|2021-05-0210:01:01|2021-05-0210:30:01|81||3|1001|9001|2021-06-0219:01:01|2021-06-0219:31:01|84||4|1001|9002|2021-09-0519:01:01|2021-09-0519:40:01|89||5|1001|9001|2021-09-0212:01:01|NULL|NULL||6|1001|9002|2021-09-0112:01:01|NULL|NULL||7|1002|9002|2021-02-0219:01:01|2021-02-0219:30:01|87||8|1002|9001|2021-05-0518:01:01|2021-05-0518:59:02|90||9|1003|9001|2021-02-0612:01:01|NULL|NULL||10|1003|9001|2021-09-0710:01:01|2021-09-0710:31:01|89||11|1004|9001|2021-09-0612:01:01|NULL|NULL|+----+------+---------+---------------------+---------------------+-------+

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

🚀 建表语句
droptableifexists exam_record;CREATETABLE exam_record (
    id intPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    uid intNOTNULLCOMMENT'用户ID',
    exam_id intNOTNULLCOMMENT'试卷ID',
    start_time datetimeNOTNULLCOMMENT'开始时间',
    submit_time datetimeCOMMENT'提交时间',
    score tinyintCOMMENT'得分')CHARACTERSET utf8 COLLATE utf8_general_ci;INSERTINTO 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);

🍌🍌 答案
selectDISTINCTcount(*)as total_pv,count(submit_time)as compelete_pv,count(distinct exam_id and score ISnotNULL)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-0209:01:01|2020-01-0209:21:01|80||2|1002|9001|2021-09-0519:01:01|2021-09-0519:40:01|89||3|1002|9002|2021-09-0212:01:01|NULL|NULL||4|1002|9003|2021-09-0112:01:01|NULL|NULL||5|1002|9001|2021-02-0219:01:01|2021-02-0219:30:01|87||6|1002|9002|2021-05-0518:01:01|2021-05-0518:59:02|90||7|1003|9002|2021-02-0612:01:01|NULL|NULL||8|1003|9003|2021-09-0710:01:01|2021-09-0710:31:01|86||9|1004|9003|2021-09-0612: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-0110:00:00||2|9002|SQL| easy       |60|2020-02-0110:00:00||3|9003| 算法   | medium     |80|2020-08-0210:00:00|+----+---------+--------+------------+----------+---------------------+

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

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

🚀 建表语句
droptableifexists examination_info;CREATETABLE  examination_info (
    id intPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    exam_id intUNIQUENOTNULLCOMMENT'试卷ID',
    tag varchar(32)COMMENT'类别标签',
    difficulty varchar(8)COMMENT'难度',
    duration intNOTNULLCOMMENT'时长',
    release_time datetimeCOMMENT'发布时间')CHARACTERSET utf8 COLLATE utf8_general_ci;droptableifexists exam_record;CREATETABLE  exam_record (
    id intPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    uid intNOTNULLCOMMENT'用户ID',
    exam_id intNOTNULLCOMMENT'试卷ID',
    start_time datetimeNOTNULLCOMMENT'开始时间',
    submit_time datetimeCOMMENT'提交时间',
    score tinyintCOMMENT'得分')CHARACTERSET utf8 COLLATE utf8_general_ci;INSERTINTO 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');INSERTINTO 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);

🍌🍌 答案
selectmin(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 >=(selectavg(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-0209:01:01|2021-07-0209:21:01|80||2|1002|9001|2021-09-0519:01:01|2021-09-0519:40:01|81||3|1002|9002|2021-09-0212:01:01|NULL|NULL||4|1002|9003|2021-09-0112:01:01|NULL|NULL||5|1002|9001|2021-07-0219:01:01|2021-07-0219:30:01|82||6|1002|9002|2021-07-0518:01:01|2021-07-0518:59:02|90||7|1003|9002|2021-07-0612:01:01|NULL|NULL||8|1003|9003|2021-09-0710:01:01|2021-09-0710:31:01|86||9|1004|9003|2021-09-0612:01:01|NULL|NULL||10|1002|9003|2021-09-0112:01:01|2021-09-0112:31:01|81||11|1005|9001|2021-09-0112:01:01|2021-09-0112:31:01|88||12|1006|9002|2021-09-0212:11:01|2021-09-0212:31:01|89||13|1007|9002|2020-09-0212:11:01|2020-09-0212: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位小数。
注：此处活跃指有交卷行为。

🚀 建表语句
droptableifexists exam_record;CREATETABLE exam_record (
    id intPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    uid intNOTNULLCOMMENT'用户ID',
    exam_id intNOTNULLCOMMENT'试卷ID',
    start_time datetimeNOTNULLCOMMENT'开始时间',
    submit_time datetimeCOMMENT'提交时间',
    score tinyintCOMMENT'得分')CHARACTERSET utf8 COLLATE utf8_general_ci;INSERTINTO 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))asmonth,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 isnotnullandyear(submit_time)='2021'groupbymonth;

在这里插入图片描述

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

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

🚀 题目描述
现有一张题目练习记录表practice_record，示例内容如下：
+----+------+-------------+---------------------+-------+| id | uid  | question_id | submit_time         | score |+----+------+-------------+---------------------+-------+|1|1001|8001|2021-08-0211:41:01|60||2|1002|8001|2021-09-0219:30:01|50||3|1002|8001|2021-09-0219:20:01|70||4|1002|8002|2021-09-0219:38:01|70||5|1003|8002|2021-08-0119: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）

🚀 建表语句
droptableifexists practice_record;CREATETABLE  practice_record (
    id intPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    uid intNOTNULLCOMMENT'用户ID',
    question_id intNOTNULLCOMMENT'题目ID',
    submit_time datetimeCOMMENT'提交时间',
    score tinyintCOMMENT'得分')CHARACTERSET utf8 COLLATE utf8_general_ci;INSERTINTO 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
WHEREYEAR(submit_time)=2021GROUPBY sm WITH ROLLUPORDERBY 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-0209:01:01|2021-07-0209:21:01|80||2|1002|9001|2021-09-0519:01:01|2021-09-0519:40:01|81||3|1002|9002|2021-09-0212:01:01|NULL|NULL||4|1002|9003|2021-09-0112:01:01|NULL|NULL||5|1002|9001|2021-07-0219:01:01|2021-07-0219:30:01|82||6|1002|9002|2021-07-0518:01:01|2021-07-0518:59:02|90||7|1003|9002|2021-07-0612:01:01|NULL|NULL||8|1003|9003|2021-09-0710:01:01|2021-09-0710:31:01|86||9|1004|9003|2021-09-0612:01:01|NULL|NULL||10|1002|9003|2021-09-0112:01:01|2021-09-0112:31:01|81||11|1005|9001|2021-09-0112:01:01|2021-09-0112:31:01|88||12|1005|9002|2021-09-0112:01:01|2021-09-0112:31:01|88||13|1006|9002|2021-09-0212:11:01|2021-09-0212: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-0110:00:00||2|9002|SQL| easy       |60|2020-02-0110:00:00||3|9003| 算法   | medium     |80|2020-08-0210: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间用:连接，多元素间用;连接。

🚀 建表语句
droptableifexists examination_info;CREATETABLE examination_info (
    id intPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    exam_id intUNIQUENOTNULLCOMMENT'试卷ID',
    tag varchar(32)COMMENT'类别标签',
    difficulty varchar(8)COMMENT'难度',
    duration intNOTNULLCOMMENT'时长',
    release_time datetimeCOMMENT'发布时间')CHARACTERSET utf8 COLLATE utf8_general_ci;droptableifexists exam_record;CREATETABLE  exam_record (
    id intPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    uid intNOTNULLCOMMENT'用户ID',
    exam_id intNOTNULLCOMMENT'试卷ID',
    start_time datetimeNOTNULLCOMMENT'开始时间',
    submit_time datetimeCOMMENT'提交时间',
    score tinyintCOMMENT'得分')CHARACTERSET utf8 COLLATE utf8_general_ci;INSERTINTO 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');INSERTINTO 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 isnull,1,null)as incomplete,if(submit_time isnull,null,1)as complete
    from exam_record 
    leftjoin examination_info using(exam_id)whereyear(start_time)=2021)as exam_complete_rec
groupby uid
having complete_cnt >=1and incomplete_cnt BETWEEN2and4orderby incomplete_cnt DESC;

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

标签： sql 数据库 dba
本文转载自: https://blog.csdn.net/weixin_41645135/article/details/125126144
版权归原作者 IT邦德 所有， 如有侵权，请联系我们删除。
登录后发布评论

“【牛客刷题-SQL进阶挑战】NO3.聚合分组查询”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

机器人技术在安全领域的重要作用

安全学习记录——网络篇（一）

图腾柱PFC：HP1010为您的电动两轮车之旅提供绿色，高效，安全的动力

单元测试数据库回滚问题

这才是单元测试，之前我们都错了

【C语言基础】：字符函数和字符串函数

ChatGPT-Next-Web SSRF漏洞+XSS漏洞复现(CVE-2023-49785)

文章导航