题目来源于：牛客题霸 - SQL 进阶挑战
题目来源于：牛客题霸 - SQL 进阶挑战
较难或者困难的题目可以根据自身实际情况和面试需要来决定是否要跳过。
聚合函数
SQL 类别高难度试卷得分的截断平均值（较难）
描述： 牛客的运营同学想要查看大家在 SQL 类别中高难度试卷的得分情况。
请你帮她从exam_record数据表中计算所有用户完成 SQL 类别高难度试卷得分的截断平均值（去掉一个最大值和一个最小值后的平均值）。
exam_record示例数据：examination_info（exam_id 试卷 ID, tag 试卷类别, difficulty 试卷难度, duration 考试时长, release_time 发布时间）
examination_infoexam_iddifficultydurationrelease_timeidexam_idtagdifficultydurationrelease_time19001SQLhard602020-01-01 10:00:0029002算法medium802020-08-02 10:00:00
idexam_idtagdifficultydurationrelease_time
id
exam_id
tag
difficulty
duration
release_time
19001SQLhard602020-01-01 10:00:00
1
9001
SQL
hard
60
2020-01-01 10:00:00
29002算法medium802020-08-02 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 得分）
exam_recordiduidexam_idstart_timesubmit_timescore1100190012020-01-02 09:01:012020-01-02 09:21:01802100190012021-05-02 10:01:012021-05-02 10:30:01813100190012021-06-02 19:01:012021-06-02 19:31:01844100190022021-09-05 19:01:012021-09-05 19:40:01895100190012021-09-02 12:01:01(NULL)(NULL)6100190022021-09-01 12:01:01(NULL)(NULL)7100290022021-02-02 19:01:012021-02-02 19:30:01878100290012021-05-05 18:01:012021-05-05 18:59:02909100390012021-09-07 12:01:012021-09-07 10:31:015010100490012021-09-06 10:01:01(NULL)(NULL)
iduidexam_idstart_timesubmit_timescore
id
uid
exam_id
start_time
submit_time
score
1100190012020-01-02 09:01:012020-01-02 09:21:0180
1
1001
9001
2020-01-02 09:01:01
2020-01-02 09:21:01
80
2100190012021-05-02 10:01:012021-05-02 10:30:0181
2
1001
9001
2021-05-02 10:01:01
2021-05-02 10:30:01
81
3100190012021-06-02 19:01:012021-06-02 19:31:0184
3
1001
9001
2021-06-02 19:01:01
2021-06-02 19:31:01
84
4100190022021-09-05 19:01:012021-09-05 19:40:0189
4
1001
9002
2021-09-05 19:01:01
2021-09-05 19:40:01
89
5100190012021-09-02 12:01:01(NULL)(NULL)
5
1001
9001
2021-09-02 12:01:01
(NULL)
(NULL)
6100190022021-09-01 12:01:01(NULL)(NULL)
6
1001
9002
2021-09-01 12:01:01
(NULL)
(NULL)
7100290022021-02-02 19:01:012021-02-02 19:30:0187
7
1002
9002
2021-02-02 19:01:01
2021-02-02 19:30:01
87
8100290012021-05-05 18:01:012021-05-05 18:59:0290
8
1002
9001
2021-05-05 18:01:01
2021-05-05 18:59:02
90
9100390012021-09-07 12:01:012021-09-07 10:31:0150
9
1003
9001
2021-09-07 12:01:01
2021-09-07 10:31:01
50
10100490012021-09-06 10:01:01(NULL)(NULL)
10
1004
9001
2021-09-06 10:01:01
(NULL)
(NULL)
根据输入你的查询结果如下：
tagdifficultyclip_avg_scoreSQLhard81.7
tagdifficultyclip_avg_score
tag
difficulty
clip_avg_score
SQLhard81.7
SQL
hard
81.7
从examination_info表可知，试卷 9001 为高难度 SQL 试卷，该试卷被作答的得分有[80,81,84,90,50]，去除最高分和最低分后为[80,81,84]，平均分为 81.6666667，保留一位小数后为 81.7
examination_info输入描述：
输入数据中至少有 3 个有效分数
思路一： 要找出高难度 sql 试卷，肯定需要联 examination_info 这张表，然后找出高难度的课程，由 examination_info 得知，高难度 sql 的 exam_id 为 9001，那么等下就以 exam_id = 9001 作为条件去查询；
先找出 9001 号考试 select * from exam_record where exam_id = 9001
select * from exam_record where exam_id = 9001然后，找出最高分 select max(score) 最高分 from exam_record where exam_id = 9001
select max(score) 最高分 from exam_record where exam_id = 9001接着，找出最低分 select min(score) 最低分 from exam_record where exam_id = 9001
select min(score) 最低分 from exam_record where exam_id = 9001在查询出来的分数结果集当中，去掉最高分和最低分，最直观能想到的就是 NOT IN 或者 用 NOT EXISTS 也行，这里以 NOT IN 来做
首先将主体写出来select tag, difficulty, round(avg(score), 1) clip_avg_score from examination_info info INNER JOIN exam_record record
select tag, difficulty, round(avg(score), 1) clip_avg_score from examination_info info INNER JOIN exam_record record小 tips : MYSQL 的 ROUND() 函数 ,ROUND(X)返回参数 X 最近似的整数 ROUND(X,D)返回 X ,其值保留到小数点后 D 位,第 D 位的保留方式为四舍五入。
ROUND()ROUND(X)ROUND(X,D)再将上面的 "碎片" 语句拼凑起来即可， 注意在 NOT IN 中两个子查询用 UNION ALL 来关联，用 union 把 max 和 min 的结果集中在一行当中，这样形成一列多行的效果。
答案一：
SELECT tag, difficulty, ROUND(AVG(score), 1) clip_avg_score
FROM examination_info info INNER JOIN exam_record record
WHERE info.exam_id = record.exam_id
AND record.exam_id = 9001
AND record.score NOT IN(
SELECT MAX(score)
FROM exam_record
WHERE exam_id = 9001
UNION ALL
SELECT MIN(score)
FROM exam_record
WHERE exam_id = 9001
)SELECT tag, difficulty, ROUND(AVG(score), 1) clip_avg_score
FROM examination_info info INNER JOIN exam_record record
WHERE info.exam_id = record.exam_id
AND record.exam_id = 9001
AND record.score NOT IN(
SELECT MAX(score)
FROM exam_record
WHERE exam_id = 9001
UNION ALL
SELECT MIN(score)
FROM exam_record
WHERE exam_id = 9001
)这是最直观，也是最容易想到的解法，但是还有待改进，这算是投机取巧过关，其实严格按照题目要求应该这么写：
SELECT tag,
difficulty,
ROUND(AVG(score), 1) clip_avg_score
FROM examination_info info
INNER JOIN exam_record record
WHERE info.exam_id = record.exam_id
AND record.exam_id =
(SELECT examination_info.exam_id
FROM examination_info
WHERE tag = 'SQL'
AND difficulty = 'hard' )
AND record.score NOT IN
(SELECT MAX(score)
FROM exam_record
WHERE exam_id =
(SELECT examination_info.exam_id
FROM examination_info
WHERE tag = 'SQL'
AND difficulty = 'hard' )
UNION ALL SELECT MIN(score)
FROM exam_record
WHERE exam_id =
(SELECT examination_info.exam_id
FROM examination_info
WHERE tag = 'SQL'
AND difficulty = 'hard' ) )SELECT tag,
difficulty,
ROUND(AVG(score), 1) clip_avg_score
FROM examination_info info
INNER JOIN exam_record record
WHERE info.exam_id = record.exam_id
AND record.exam_id =
(SELECT examination_info.exam_id
FROM examination_info
WHERE tag = 'SQL'
AND difficulty = 'hard' )
AND record.score NOT IN
(SELECT MAX(score)
FROM exam_record
WHERE exam_id =
(SELECT examination_info.exam_id
FROM examination_info
WHERE tag = 'SQL'
AND difficulty = 'hard' )
UNION ALL SELECT MIN(score)
FROM exam_record
WHERE exam_id =
(SELECT examination_info.exam_id
FROM examination_info
WHERE tag = 'SQL'
AND difficulty = 'hard' ) )然而你会发现，重复的语句非常多，所以可以利用WITH来抽取公共部分
WITHWITH 子句介绍：
WITHWITH 子句，也称为公共表表达式（Common Table Expression，CTE），是在 SQL 查询中定义临时表的方式。它可以让我们在查询中创建一个临时命名的结果集，并且可以在同一查询中引用该结果集。
WITH基本用法：
WITH cte_name (column1, column2, ..., columnN) AS (
-- 查询体
SELECT ...
FROM ...
WHERE ...
)
-- 主查询
SELECT ...
FROM cte_name
WHERE ...WITH cte_name (column1, column2, ..., columnN) AS (
-- 查询体
SELECT ...
FROM ...
WHERE ...
)
-- 主查询
SELECT ...
FROM cte_name
WHERE ...WITH 子句由以下几个部分组成：
WITHcte_name: 给临时表起一个名称，可以在主查询中引用。
cte_name(column1, column2, ..., columnN): 可选，指定临时表的列名。
(column1, column2, ..., columnN)AS: 必需，表示开始定义临时表。
ASCTE 查询体: 实际的查询语句，用于定义临时表中的数据。
CTE 查询体WITH 子句的主要用途之一是增强查询的可读性和可维护性，尤其在涉及多个嵌套子查询或需要重复使用相同的查询逻辑时。通过将这些逻辑放在一个命名的临时表中，我们可以更清晰地组织查询，并消除重复代码。
WITH此外，WITH 子句还可以在复杂的查询中实现递归查询。递归查询允许我们在单个查询中执行对同一表的多次迭代，逐步构建结果集。这在处理层次结构数据、组织结构和树状结构等场景中非常有用。
WITH小细节：MySQL 5.7 版本以及之前的版本不支持在 WITH 子句中直接使用别名。
WITH下面是改进后的答案：
WITH t1 AS
(SELECT record.*,
info.tag,
info.difficulty
FROM exam_record record
INNER JOIN examination_info info ON record.exam_id = info.exam_id
WHERE info.tag = "SQL"
AND info.difficulty = "hard" )
SELECT tag,
difficulty,
ROUND(AVG(score), 1)
FROM t1
WHERE score NOT IN
(SELECT max(score)
FROM t1
UNION SELECT min(score)
FROM t1)WITH t1 AS
(SELECT record.*,
info.tag,
info.difficulty
FROM exam_record record
INNER JOIN examination_info info ON record.exam_id = info.exam_id
WHERE info.tag = "SQL"
AND info.difficulty = "hard" )
SELECT tag,
difficulty,
ROUND(AVG(score), 1)
FROM t1
WHERE score NOT IN
(SELECT max(score)
FROM t1
UNION SELECT min(score)
FROM t1)思路二：
筛选 SQL 高难度试卷：where tag="SQL" and difficulty="hard"
where tag="SQL" and difficulty="hard"计算截断平均值：(和-最大值-最小值) / (总个数-2): (sum(score) - max(score) - min(score)) / (count(score) - 2)有一个缺点就是，如果最大值和最小值有多个，这个方法就很难筛选出来, 但是题目中说了----->去掉一个最大值和一个最小值后的平均值, 所以这里可以用这个公式。
(和-最大值-最小值) / (总个数-2)(sum(score) - max(score) - min(score)) / (count(score) - 2)
(sum(score) - max(score) - min(score)) / (count(score) - 2)有一个缺点就是，如果最大值和最小值有多个，这个方法就很难筛选出来, 但是题目中说了----->去掉一个最大值和一个最小值后的平均值, 所以这里可以用这个公式。
去掉一个最大值和一个最小值后的平均值答案二：
SELECT info.tag,
info.difficulty,
ROUND((SUM(record.score)- MIN(record.score)- MAX(record.score)) / (COUNT(record.score)- 2), 1) AS clip_avg_score
FROM examination_info info,
exam_record record
WHERE info.exam_id = record.exam_id
AND info.tag = "SQL"
AND info.difficulty = "hard";SELECT info.tag,
info.difficulty,
ROUND((SUM(record.score)- MIN(record.score)- MAX(record.score)) / (COUNT(record.score)- 2), 1) AS clip_avg_score
FROM examination_info info,
exam_record record
WHERE info.exam_id = record.exam_id
AND info.tag = "SQL"
AND info.difficulty = "hard";统计作答次数
有一个试卷作答记录表 exam_record，请从中统计出总作答次数 total_pv、试卷已完成作答数 complete_pv、已完成的试卷数 complete_exam_cnt。
exam_recordtotal_pvcomplete_pvcomplete_exam_cnt示例数据 exam_record 表（uid 用户 ID, exam_id 试卷 ID, start_time 开始作答时间, submit_time 交卷时间, score 得分）：
exam_recorduidexam_idstart_timesubmit_timescoreiduidexam_idstart_timesubmit_timescore1100190012020-01-02 09:01:012020-01-02 09:21:01802100190012021-05-02 10:01:012021-05-02 10:30:01813100190012021-06-02 19:01:012021-06-02 19:31:01844100190022021-09-05 19:01:012021-09-05 19:40:01895100190012021-09-02 12:01:01(NULL)(NULL)6100190022021-09-01 12:01:01(NULL)(NULL)7100290022021-02-02 19:01:012021-02-02 19:30:01878100290012021-05-05 18:01:012021-05-05 18:59:02909100390012021-09-07 12:01:012021-09-07 10:31:015010100490012021-09-06 10:01:01(NULL)(NULL)
iduidexam_idstart_timesubmit_timescore
id
uid
exam_id
start_time
submit_time
score
1100190012020-01-02 09:01:012020-01-02 09:21:0180
1
1001
9001
2020-01-02 09:01:01
2020-01-02 09:21:01
80
2100190012021-05-02 10:01:012021-05-02 10:30:0181
2
1001
9001
2021-05-02 10:01:01
2021-05-02 10:30:01
81
3100190012021-06-02 19:01:012021-06-02 19:31:0184
3
1001
9001
2021-06-02 19:01:01
2021-06-02 19:31:01
84
4100190022021-09-05 19:01:012021-09-05 19:40:0189
4
1001
9002
2021-09-05 19:01:01
2021-09-05 19:40:01
89
5100190012021-09-02 12:01:01(NULL)(NULL)
5
1001
9001
2021-09-02 12:01:01
(NULL)
(NULL)
6100190022021-09-01 12:01:01(NULL)(NULL)
6
1001
9002
2021-09-01 12:01:01
(NULL)
(NULL)
7100290022021-02-02 19:01:012021-02-02 19:30:0187
7
1002
9002
2021-02-02 19:01:01
2021-02-02 19:30:01
87
8100290012021-05-05 18:01:012021-05-05 18:59:0290
8
1002
9001
2021-05-05 18:01:01
2021-05-05 18:59:02
90
9100390012021-09-07 12:01:012021-09-07 10:31:0150
9
1003
9001
2021-09-07 12:01:01
2021-09-07 10:31:01
50
10100490012021-09-06 10:01:01(NULL)(NULL)
10
1004
9001
2021-09-06 10:01:01
(NULL)
(NULL)
示例输出：
total_pvcomplete_pvcomplete_exam_cnt1072
total_pvcomplete_pvcomplete_exam_cnt
total_pv
complete_pv
complete_exam_cnt
1072
10
7
2
解释：表示截止当前，有 10 次试卷作答记录，已完成的作答次数为 7 次（中途退出的为未完成状态，其交卷时间和份数为 NULL），已完成的试卷有 9001 和 9002 两份。
思路： 这题一看到统计次数，肯定第一时间就要想到用COUNT这个函数来解决，问题是要统计不同的记录，该怎么来写？使用子查询就能解决这个题目(这题用 case when 也能写出来，解法类似，逻辑不同而已)；首先在做这个题之前，让我们先来了解一下COUNT的基本用法；
COUNTCOUNTCOUNT() 函数的基本语法如下所示：
COUNT()COUNT(expression)COUNT(expression)其中，expression 可以是列名、表达式、常量或通配符。下面是一些常见的用法示例：
expression计算表中所有行的数量：
SELECT COUNT(*) FROM table_name;SELECT COUNT(*) FROM table_name;计算特定列非空（不为 NULL）值的数量：
SELECT COUNT(column_name) FROM table_name;SELECT COUNT(column_name) FROM table_name;计算满足条件的行数：
SELECT COUNT(*) FROM table_name WHERE condition;SELECT COUNT(*) FROM table_name WHERE condition;结合 GROUP BY 使用，计算分组后每个组的行数：
GROUP BYSELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;计算不同列组合的唯一组合数：
SELECT COUNT(DISTINCT column_name1, column_name2) FROM table_name;SELECT COUNT(DISTINCT column_name1, column_name2) FROM table_name;在使用 COUNT() 函数时，如果不指定任何参数或者使用 COUNT(*)，将会计算所有行的数量。而如果使用列名，则只会计算该列非空值的数量。
COUNT()COUNT(*)另外，COUNT() 函数的结果是一个整数值。即使结果是零，也不会返回 NULL，这点需要谨记。
COUNT()答案：
SELECT
count(*) total_pv,
( SELECT count(*) FROM exam_record WHERE submit_time IS NOT NULL ) complete_pv,
( SELECT COUNT( DISTINCT exam_id, score IS NOT NULL OR NULL ) FROM exam_record ) complete_exam_cnt
FROM
exam_recordSELECT
count(*) total_pv,
( SELECT count(*) FROM exam_record WHERE submit_time IS NOT NULL ) complete_pv,
( SELECT COUNT( DISTINCT exam_id, score IS NOT NULL OR NULL ) FROM exam_record ) complete_exam_cnt
FROM
exam_record这里着重说一下COUNT( DISTINCT exam_id, score IS NOT NULL OR NULL )这一句，判断 score 是否为 null ，如果是即为真，如果不是返回 null；注意这里如果不加 or null 在不是 null 的情况下只会返回 false 也就是返回 0；
COUNT( DISTINCT exam_id, score IS NOT NULL OR NULL )or nullCOUNT本身是不可以对多列求行数的，distinct的加入是的多列成为一个整体，可以求出现的行数了;count distinct在计算时只返回非 null 的行, 这个也要注意；
COUNTdistinctcount distinct另外通过本题 get 到了------>count 加条件常用句式count( 列判断 or null)
count( 列判断 or null)得分不小于平均分的最低分
描述： 请从试卷作答记录表中找到 SQL 试卷得分不小于该类试卷平均得分的用户最低得分。
示例数据 exam_record 表（uid 用户 ID, exam_id 试卷 ID, start_time 开始作答时间, submit_time 交卷时间, score 得分）：
iduidexam_idstart_timesubmit_timescore1100190012020-01-02 09:01:012020-01-02 09:21:01802100290012021-09-05 19:01:012021-09-05 19:40:01893100290022021-09-02 12:01:01(NULL)(NULL)4100290032021-09-01 12:01:01(NULL)(NULL)5100290012021-02-02 19:01:012021-02-02 19:30:01876100290022021-05-05 18:01:012021-05-05 18:59:02907100390022021-02-06 12:01:01(NULL)(NULL)8100390032021-09-07 10:01:012021-09-07 10:31:01869100490032021-09-06 12:01:01(NULL)(NULL)
iduidexam_idstart_timesubmit_timescore
id
uid
exam_id
start_time
submit_time
score
1100190012020-01-02 09:01:012020-01-02 09:21:0180
1
1001
9001
2020-01-02 09:01:01
2020-01-02 09:21:01
80
2100290012021-09-05 19:01:012021-09-05 19:40:0189
2
1002
9001
2021-09-05 19:01:01
2021-09-05 19:40:01
89
3100290022021-09-02 12:01:01(NULL)(NULL)
3
1002
9002
2021-09-02 12:01:01
(NULL)
(NULL)
4100290032021-09-01 12:01:01(NULL)(NULL)
4
1002
9003
2021-09-01 12:01:01
(NULL)
(NULL)
5100290012021-02-02 19:01:012021-02-02 19:30:0187
5
1002
9001
2021-02-02 19:01:01
2021-02-02 19:30:01
87
6100290022021-05-05 18:01:012021-05-05 18:59:0290
6
1002
9002
2021-05-05 18:01:01
2021-05-05 18:59:02
90
7100390022021-02-06 12:01:01(NULL)(NULL)
7
1003
9002
2021-02-06 12:01:01
(NULL)
(NULL)
8100390032021-09-07 10:01:012021-09-07 10:31:0186
8
1003
9003
2021-09-07 10:01:01
2021-09-07 10:31:01
86
9100490032021-09-06 12:01:01(NULL)(NULL)
9
1004
9003
2021-09-06 12:01:01
(NULL)
(NULL)
examination_info 表（exam_id 试卷 ID, tag 试卷类别, difficulty 试卷难度, duration 考试时长, release_time 发布时间）
examination_infoexam_idtagdifficultydurationrelease_timeidexam_idtagdifficultydurationrelease_time19001SQLhard602020-01-01 10:00:0029002SQLeasy602020-02-01 10:00:0039003算法medium802020-08-02 10:00:00
idexam_idtagdifficultydurationrelease_time
id
exam_id
tag
difficulty
duration
release_time
19001SQLhard602020-01-01 10:00:00
1
9001
SQL
hard
60
2020-01-01 10:00:00
29002SQLeasy602020-02-01 10:00:00
2
9002
SQL
easy
60
2020-02-01 10:00:00
39003算法medium802020-08-02 10:00:00
3
9003
算法
medium
80
2020-08-02 10:00:00
示例输出数据：
min_score_over_avg87
min_score_over_avg
min_score_over_avg
87
87
解释：试卷 9001 和 9002 为 SQL 类别，作答这两份试卷的得分有[80,89,87,90]，平均分为 86.5，不小于平均分的最小分数为 87
思路：这类题目第一眼看确实很复杂， 因为不知道从哪入手，但是当我们仔细读题审题后，要学会抓住题干中的关键信息。以本题为例：请从试卷作答记录表中找到SQL试卷得分不小于该类试卷平均得分的用户最低得分。你能一眼从中提取哪些有效信息来作为解题思路？
请从试卷作答记录表中找到SQL试卷得分不小于该类试卷平均得分的用户最低得分。第一条：找到==SQL==试卷得分
第二条：该类试卷==平均得分==
第三条：该类试卷的==用户最低得分==
然后中间的 “桥梁” 就是==不小于==
将条件拆分后，先逐步完成
-- 找出tag为‘SQL’的得分 【80, 89,87,90】
-- 再算出这一组的平均得分
select ROUND(AVG(score), 1) from examination_info info INNER JOIN exam_record record
where info.exam_id = record.exam_id
and tag= 'SQL'-- 找出tag为‘SQL’的得分 【80, 89,87,90】
-- 再算出这一组的平均得分
select ROUND(AVG(score), 1) from examination_info info INNER JOIN exam_record record
where info.exam_id = record.exam_id
and tag= 'SQL'然后再找出该类试卷的最低得分，接着将结果集【80, 89,87,90】 去和平均分数作比较，方可得出最终答案。
【80, 89,87,90】答案：
SELECT MIN(score) AS min_score_over_avg
FROM examination_info info
INNER JOIN exam_record record
WHERE info.exam_id = record.exam_id
AND tag= 'SQL'
AND score >=
(SELECT ROUND(AVG(score), 1)
FROM examination_info info
INNER JOIN exam_record record
WHERE info.exam_id = record.exam_id
AND tag= 'SQL' )SELECT MIN(score) AS min_score_over_avg
FROM examination_info info
INNER JOIN exam_record record
WHERE info.exam_id = record.exam_id
AND tag= 'SQL'
AND score >=
(SELECT ROUND(AVG(score), 1)
FROM examination_info info
INNER JOIN exam_record record
WHERE info.exam_id = record.exam_id
AND tag= 'SQL' )其实这类题目给出的要求看似很 “绕”，但其实仔细梳理一遍，将大条件拆分成小条件，逐个拆分完以后，最后将所有条件拼凑起来。反正只要记住：抓主干，理分支，问题便迎刃而解。
分组查询
平均活跃天数和月活人数
描述：用户在牛客试卷作答区作答记录存储在表 exam_record 中，内容如下：
exam_recordexam_record 表（uid 用户 ID, exam_id 试卷 ID, start_time 开始作答时间, submit_time 交卷时间, score 得分）
exam_recorduidexam_idstart_timesubmit_timescoreiduidexam_idstart_timesubmit_timescore1100190012021-07-02 09:01:012021-07-02 09:21:01802100290012021-09-05 19:01:012021-09-05 19:40:01813100290022021-09-02 12:01:01(NULL)(NULL)4100290032021-09-01 12:01:01(NULL)(NULL)5100290012021-07-02 19:01:012021-07-02 19:30:01826100290022021-07-05 18:01:012021-07-05 18:59:02907100390022021-07-06 12:01:01(NULL)(NULL)8100390032021-09-07 10:01:012021-09-07 10:31:01869100490032021-09-06 12:01:01(NULL)(NULL)10100290032021-09-01 12:01:012021-09-01 12:31:018111100590012021-09-01 12:01:012021-09-01 12:31:018812100690022021-09-02 12:11:012021-09-02 12:31:018913100790022020-09-02 12:11:012020-09-02 12:31:0189
iduidexam_idstart_timesubmit_timescore
id
uid
exam_id
start_time
submit_time
score
1100190012021-07-02 09:01:012021-07-02 09:21:0180
1
1001
9001
2021-07-02 09:01:01
2021-07-02 09:21:01
80
2100290012021-09-05 19:01:012021-09-05 19:40:0181
2
1002
9001
2021-09-05 19:01:01
2021-09-05 19:40:01
81
3100290022021-09-02 12:01:01(NULL)(NULL)
3
1002
9002
2021-09-02 12:01:01
(NULL)
(NULL)
4100290032021-09-01 12:01:01(NULL)(NULL)
4
1002
9003
2021-09-01 12:01:01
(NULL)
(NULL)
5100290012021-07-02 19:01:012021-07-02 19:30:0182
5
1002
9001
2021-07-02 19:01:01
2021-07-02 19:30:01
82
6100290022021-07-05 18:01:012021-07-05 18:59:0290
6
1002
9002
2021-07-05 18:01:01
2021-07-05 18:59:02
90
7100390022021-07-06 12:01:01(NULL)(NULL)
7
1003
9002
2021-07-06 12:01:01
(NULL)
(NULL)
8100390032021-09-07 10:01:012021-09-07 10:31:0186
8
1003
9003
2021-09-07 10:01:01
2021-09-07 10:31:01
86
9100490032021-09-06 12:01:01(NULL)(NULL)
9
1004
9003
2021-09-06 12:01:01
(NULL)
(NULL)
10100290032021-09-01 12:01:012021-09-01 12:31:0181
10
1002
9003
2021-09-01 12:01:01
2021-09-01 12:31:01
81
11100590012021-09-01 12:01:012021-09-01 12:31:0188
11
1005
9001
2021-09-01 12:01:01
2021-09-01 12:31:01
88
12100690022021-09-02 12:11:012021-09-02 12:31:0189
12
1006
9002
2021-09-02 12:11:01
2021-09-02 12:31:01
89
13100790022020-09-02 12:11:012020-09-02 12:31:0189
13
1007
9002
2020-09-02 12:11:01
2020-09-02 12:31:01
89
请计算 2021 年每个月里试卷作答区用户平均月活跃天数 avg_active_days 和月度活跃人数 mau，上面数据的示例输出如下：
avg_active_daysmaumonthavg_active_daysmau2021071.5022021091.254
monthavg_active_daysmau
month
avg_active_days
mau
2021071.502
202107
1.50
2
2021091.254
202109
1.25
4
解释：2021 年 7 月有 2 人活跃，共活跃了 3 天（1001 活跃 1 天，1002 活跃 2 天），平均活跃天数 1.5；2021 年 9 月有 4 人活跃，共活跃了 5 天，平均活跃天数 1.25，结果保留 2 位小数。
注：此处活跃指有==交卷==行为。
思路：读完题先注意高亮部分；一般求天数和月活跃人数马上就要想到相关的日期函数；这一题我们同样来进行拆分，把问题细化再解决；首先求活跃人数，肯定要用到COUNT()，那这里首先就有一个坑，不知道大家注意了没有？用户 1002 在 9 月份做了两种不同的试卷，所以这里要注意去重，不然在统计的时候，活跃人数是错的；第二个就是要知道日期的格式化，如上表，题目要求以202107这种日期格式展现，要用到DATE_FORMAT来进行格式化。
COUNT()202107DATE_FORMAT基本用法：
DATE_FORMAT(date_value, format)
DATE_FORMAT(date_value, format)date_value 参数是待格式化的日期或时间值。
date_valueformat 参数是指定的日期或时间格式（这个和 Java 里面的日期格式一样）。
format答案：
SELECT DATE_FORMAT(submit_time, '%Y%m') MONTH,
round(count(DISTINCT UID, DATE_FORMAT(submit_time, '%Y%m%d')) / count(DISTINCT UID), 2) avg_active_days,
COUNT(DISTINCT UID) mau
FROM exam_record
WHERE YEAR (submit_time) = 2021
GROUP BY MONTHSELECT DATE_FORMAT(submit_time, '%Y%m') MONTH,
round(count(DISTINCT UID, DATE_FORMAT(submit_time, '%Y%m%d')) / count(DISTINCT UID), 2) avg_active_days,
COUNT(DISTINCT UID) mau
FROM exam_record
WHERE YEAR (submit_time) = 2021
GROUP BY MONTH这里多说一句, 使用COUNT(DISTINCT uid, DATE_FORMAT(submit_time, '%Y%m%d')) 可以统计在 uid 列和 submit_time 列按照年份、月份和日期进行格式化后的组合值的数量。
COUNT(DISTINCT uid, DATE_FORMAT(submit_time, '%Y%m%d'))uidsubmit_time月总刷题数和日均刷题数
描述：现有一张题目练习记录表 practice_record，示例内容如下：
practice_recordiduidquestion_idsubmit_timescore1100180012021-08-02 11:41:01602100280012021-09-02 19:30:01503100280012021-09-02 19:20:01704100280022021-09-02 19:38:01705100380022021-08-01 19:38:0180
iduidquestion_idsubmit_timescore
id
uid
question_id
submit_time
score
1100180012021-08-02 11:41:0160
1
1001
8001
2021-08-02 11:41:01
60
2100280012021-09-02 19:30:0150
2
1002
8001
2021-09-02 19:30:01
50
3100280012021-09-02 19:20:0170
3
1002
8001
2021-09-02 19:20:01
70
4100280022021-09-02 19:38:0170
4
1002
8002
2021-09-02 19:38:01
70
5100380022021-08-01 19:38:0180
5
1003
8002
2021-08-01 19:38:01
80
请从中统计出 2021 年每个月里用户的月总刷题数 month_q_cnt 和日均刷题数 avg_day_q_cnt（按月份升序排序）以及该年的总体情况，示例数据输出如下：
month_q_cntavg_day_q_cntsubmit_monthmonth_q_cntavg_day_q_cnt20210820.06520210930.1002021 汇总50.161
submit_monthmonth_q_cntavg_day_q_cnt
submit_month
month_q_cnt
avg_day_q_cnt
20210820.065
202108
2
0.065
20210930.100
202109
3
0.100
2021 汇总50.161
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）
牛客已经采用最新的 Mysql 版本，如果您运行结果出现错误：ONLY_FULL_GROUP_BY，意思是：对于 GROUP BY 聚合操作，如果在 SELECT 中的列，没有在 GROUP BY 中出现，那么这个 SQL 是不合法的，因为列不在 GROUP BY 从句中，也就是说查出来的列必须在 group by 后面出现否则就会报错，或者这个字段出现在聚合函数里面。
牛客已经采用最新的 Mysql 版本，如果您运行结果出现错误：ONLY_FULL_GROUP_BY，意思是：对于 GROUP BY 聚合操作，如果在 SELECT 中的列，没有在 GROUP BY 中出现，那么这个 SQL 是不合法的，因为列不在 GROUP BY 从句中，也就是说查出来的列必须在 group by 后面出现否则就会报错，或者这个字段出现在聚合函数里面。
思路：
看到实例数据就要马上联想到相关的函数，比如submit_month就要用到DATE_FORMAT来格式化日期。然后查出每月的刷题数量。
submit_monthDATE_FORMAT每月的刷题数量
SELECT MONTH ( submit_time ), COUNT( question_id )
FROM
practice_record
GROUP BY
MONTH (submit_time)SELECT MONTH ( submit_time ), COUNT( question_id )
FROM
practice_record
GROUP BY
MONTH (submit_time)接着第三列这里要用到DAY(LAST_DAY(date_value))函数来查找给定日期的月份中的天数。
DAY(LAST_DAY(date_value))示例代码如下：
SELECT DAY(LAST_DAY('2023-07-08')) AS days_in_month;
-- 输出：31
SELECT DAY(LAST_DAY('2023-02-01')) AS days_in_month;
-- 输出：28 (闰年中的二月份)
SELECT DAY(LAST_DAY(NOW())) AS days_in_current_month;
-- 输出：31 （当前月份的天数）SELECT DAY(LAST_DAY('2023-07-08')) AS days_in_month;
-- 输出：31
SELECT DAY(LAST_DAY('2023-02-01')) AS days_in_month;
-- 输出：28 (闰年中的二月份)
SELECT DAY(LAST_DAY(NOW())) AS days_in_current_month;
-- 输出：31 （当前月份的天数）使用 LAST_DAY() 函数获取给定日期的当月最后一天，然后使用 DAY() 函数提取该日期的天数。这样就能获得指定月份的天数。
LAST_DAY()DAY()需要注意的是，LAST_DAY() 函数返回的是日期值，而 DAY() 函数用于提取日期值中的天数部分。
LAST_DAY()DAY()有了上述的分析之后，即可马上写出答案，这题复杂就复杂在处理日期上，其中的逻辑并不难。
答案：
SELECT DATE_FORMAT(submit_time, '%Y%m') submit_month,
count(question_id) month_q_cnt,
ROUND(COUNT(question_id) / DAY (LAST_DAY(submit_time)), 3) avg_day_q_cnt
FROM practice_record
WHERE DATE_FORMAT(submit_time, '%Y') = '2021'
GROUP BY submit_month
UNION ALL
SELECT '2021汇总' AS submit_month,
count(question_id) month_q_cnt,
ROUND(COUNT(question_id) / 31, 3) avg_day_q_cnt
FROM practice_record
WHERE DATE_FORMAT(submit_time, '%Y') = '2021'
ORDER BY submit_monthSELECT DATE_FORMAT(submit_time, '%Y%m') submit_month,
count(question_id) month_q_cnt,
ROUND(COUNT(question_id) / DAY (LAST_DAY(submit_time)), 3) avg_day_q_cnt
FROM practice_record
WHERE DATE_FORMAT(submit_time, '%Y') = '2021'
GROUP BY submit_month
UNION ALL
SELECT '2021汇总' AS submit_month,
count(question_id) month_q_cnt,
ROUND(COUNT(question_id) / 31, 3) avg_day_q_cnt
FROM practice_record
WHERE DATE_FORMAT(submit_time, '%Y') = '2021'
ORDER BY submit_month在实例数据输出中因为最后一行需要得出汇总数据，所以这里要 UNION ALL加到结果集中；别忘了最后要排序！
UNION ALL未完成试卷数大于 1 的有效用户（较难）
描述：现有试卷作答记录表 exam_record（uid 用户 ID, exam_id 试卷 ID, start_time 开始作答时间, submit_time 交卷时间, score 得分），示例数据如下：
exam_recorduidexam_idstart_timesubmit_timescoreiduidexam_idstart_timesubmit_timescore1100190012021-07-02 09:01:012021-07-02 09:21:01802100290012021-09-05 19:01:012021-09-05 19:40:01813100290022021-09-02 12:01:01(NULL)(NULL)4100290032021-09-01 12:01:01(NULL)(NULL)5100290012021-07-02 19:01:012021-07-02 19:30:01826100290022021-07-05 18:01:012021-07-05 18:59:02907100390022021-07-06 12:01:01(NULL)(NULL)8100390032021-09-07 10:01:012021-09-07 10:31:01869100490032021-09-06 12:01:01(NULL)(NULL)10100290032021-09-01 12:01:012021-09-01 12:31:018111100590012021-09-01 12:01:012021-09-01 12:31:018812100690022021-09-02 12:11:012021-09-02 12:31:018913100790022020-09-02 12:11:012020-09-02 12:31:0189
iduidexam_idstart_timesubmit_timescore
id
uid
exam_id
start_time
submit_time
score
1100190012021-07-02 09:01:012021-07-02 09:21:0180
1
1001
9001
2021-07-02 09:01:01
2021-07-02 09:21:01
80
2100290012021-09-05 19:01:012021-09-05 19:40:0181
2
1002
9001
2021-09-05 19:01:01
2021-09-05 19:40:01
81
3100290022021-09-02 12:01:01(NULL)(NULL)
3
1002
9002
2021-09-02 12:01:01
(NULL)
(NULL)
4100290032021-09-01 12:01:01(NULL)(NULL)
4
1002
9003
2021-09-01 12:01:01
(NULL)
(NULL)
5100290012021-07-02 19:01:012021-07-02 19:30:0182
5
1002
9001
2021-07-02 19:01:01
2021-07-02 19:30:01
82
6100290022021-07-05 18:01:012021-07-05 18:59:0290
6
1002
9002
2021-07-05 18:01:01
2021-07-05 18:59:02
90
7100390022021-07-06 12:01:01(NULL)(NULL)
7
1003
9002
2021-07-06 12:01:01
(NULL)
(NULL)
8100390032021-09-07 10:01:012021-09-07 10:31:0186
8
1003
9003
2021-09-07 10:01:01
2021-09-07 10:31:01
86
9100490032021-09-06 12:01:01(NULL)(NULL)
9
1004
9003
2021-09-06 12:01:01
(NULL)
(NULL)
10100290032021-09-01 12:01:012021-09-01 12:31:0181
10
1002
9003
2021-09-01 12:01:01
2021-09-01 12:31:01
81
11100590012021-09-01 12:01:012021-09-01 12:31:0188
11
1005
9001
2021-09-01 12:01:01
2021-09-01 12:31:01
88
12100690022021-09-02 12:11:012021-09-02 12:31:0189
12
1006
9002
2021-09-02 12:11:01
2021-09-02 12:31:01
89
13100790022020-09-02 12:11:012020-09-02 12:31:0189
13
1007
9002
2020-09-02 12:11:01
2020-09-02 12:31:01
89
还有一张试卷信息表 examination_info（exam_id 试卷 ID, tag 试卷类别, difficulty 试卷难度, duration 考试时长, release_time 发布时间），示例数据如下：
examination_infoexam_idtagdifficultydurationrelease_timeidexam_idtagdifficultydurationrelease_time19001SQLhard602020-01-01 10:00:0029002SQLeasy602020-02-01 10:00:0039003算法medium802020-08-02 10:00:00
idexam_idtagdifficultydurationrelease_time
id
exam_id
tag
difficulty
duration
release_time
19001SQLhard602020-01-01 10:00:00
1
9001
SQL
hard
60
2020-01-01 10:00:00
29002SQLeasy602020-02-01 10:00:00
2
9002
SQL
easy
60
2020-02-01 10:00:00
39003算法medium802020-08-02 10:00:00
3
9003
算法
medium
80
2020-08-02 10:00:00
请统计 2021 年每个未完成试卷作答数大于 1 的有效用户的数据（有效用户指完成试卷作答数至少为 1 且未完成数小于 5），输出用户 ID、未完成试卷作答数、完成试卷作答数、作答过的试卷 tag 集合，按未完成试卷数量由多到少排序。示例数据的输出结果如下：
uidincomplete_cntcomplete_cntdetail1002242021-09-01:算法;2021-07-02:SQL;2021-09-02:SQL;2021-09-05:SQL;2021-07-05:SQL
uidincomplete_cntcomplete_cntdetail
uid
incomplete_cnt
complete_cnt
detail
1002242021-09-01:算法;2021-07-02:SQL;2021-09-02:SQL;2021-09-05:SQL;2021-07-05:SQL
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 间用 : 连接，多元素间用 ; 连接。
思路：
仔细读题后，分析出：首先要联表，因为后面要输出tag；
tag筛选出 2021 年的数据
SELECT *
FROM exam_record er
LEFT JOIN examination_info ei ON er.exam_id = ei.exam_id
WHERE YEAR (er.start_time)= 2021SELECT *
FROM exam_record er
LEFT JOIN examination_info ei ON er.exam_id = ei.exam_id
WHERE YEAR (er.start_time)= 2021根据 uid 进行分组，然后对每个用户进行条件进行判断，题目中要求完成试卷数至少为1,未完成试卷数要大于1，小于5
完成试卷数至少为1,未完成试卷数要大于1，小于5那么等会儿写 sql 的时候条件应该是：未完成 > 1 and 已完成 >=1 and 未完成 < 5
未完成 > 1 and 已完成 >=1 and 未完成 < 5因为最后要用到字符串的拼接，而且还要组合拼接，这个可以用GROUP_CONCAT函数，下面简单介绍一下该函数的用法：
GROUP_CONCAT基本格式：
GROUP_CONCAT([DISTINCT] expr [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [, ...]] [SEPARATOR sep])GROUP_CONCAT([DISTINCT] expr [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [, ...]] [SEPARATOR sep])expr：要连接的列或表达式。
exprDISTINCT：可选参数，用于去重。当指定了 DISTINCT，相同的值只会出现一次。
DISTINCTDISTINCTORDER BY：可选参数，用于排序连接后的值。可以选择升序 (ASC) 或降序 (DESC) 排序。
ORDER BYASCDESCSEPARATOR sep：可选参数，用于设置连接后的值的分隔符。（本题要用这个参数设置 ; 号 ）
SEPARATOR sepGROUP_CONCAT() 函数常用于 GROUP BY 子句中，将一组行的值连接为一个字符串，并在结果集中以聚合的形式返回。
GROUP_CONCAT()GROUP BY答案：
SELECT a.uid,
SUM(CASE
WHEN a.submit_time IS NULL THEN 1
END) AS incomplete_cnt,
SUM(CASE
WHEN a.submit_time IS NOT NULL THEN 1
END) AS complete_cnt,
GROUP_CONCAT(DISTINCT CONCAT(DATE_FORMAT(a.start_time, '%Y-%m-%d'), ':', b.tag)
ORDER BY start_time SEPARATOR ";") AS detail
FROM exam_record a
LEFT JOIN examination_info b ON a.exam_id = b.exam_id
WHERE YEAR (a.start_time)= 2021
GROUP BY a.uid
HAVING incomplete_cnt > 1
AND complete_cnt >= 1
AND incomplete_cnt < 5
ORDER BY incomplete_cnt DESCSELECT a.uid,
SUM(CASE
WHEN a.submit_time IS NULL THEN 1
END) AS incomplete_cnt,
SUM(CASE
WHEN a.submit_time IS NOT NULL THEN 1
END) AS complete_cnt,
GROUP_CONCAT(DISTINCT CONCAT(DATE_FORMAT(a.start_time, '%Y-%m-%d'), ':', b.tag)
ORDER BY start_time SEPARATOR ";") AS detail
FROM exam_record a
LEFT JOIN examination_info b ON a.exam_id = b.exam_id
WHERE YEAR (a.start_time)= 2021
GROUP BY a.uid
HAVING incomplete_cnt > 1
AND complete_cnt >= 1
AND incomplete_cnt < 5
ORDER BY incomplete_cnt DESCSUM(CASE WHEN a.submit_time IS NULL THEN 1 END) 统计了每个用户未完成的记录数量。
SUM(CASE WHEN a.submit_time IS NULL THEN 1 END)SUM(CASE WHEN a.submit_time IS NOT NULL THEN 1 END) 统计了每个用户已完成的记录数量。
SUM(CASE WHEN a.submit_time IS NOT NULL THEN 1 END)GROUP_CONCAT(DISTINCT CONCAT(DATE_FORMAT(a.start_time, '%Y-%m-%d'), ':', b.tag) ORDER BY a.start_time SEPARATOR ';') 将每个用户的考试日期和标签以逗号分隔的形式连接成一个字符串，并按考试开始时间进行排序。
GROUP_CONCAT(DISTINCT CONCAT(DATE_FORMAT(a.start_time, '%Y-%m-%d'), ':', b.tag) ORDER BY a.start_time SEPARATOR ';')嵌套子查询
月均完成试卷数不小于 3 的用户爱作答的类别（较难）
描述：现有试卷作答记录表 exam_record（uid：用户 ID, exam_id：试卷 ID, start_time：开始作答时间, submit_time：交卷时间，没提交的话为 NULL, score：得分），示例数据如下：
exam_recorduidexam_idstart_timesubmit_timescoreiduidexam_idstart_timesubmit_timescore1100190012021-07-02 09:01:01(NULL)(NULL)2100290032021-09-01 12:01:012021-09-01 12:21:01603100290022021-09-02 12:01:012021-09-02 12:31:01704100290012021-09-05 19:01:012021-09-05 19:40:01815100290022021-07-06 12:01:01(NULL)(NULL)6100390032021-09-07 10:01:012021-09-07 10:31:01867100390032021-09-08 12:01:012021-09-08 12:11:01408100390012021-09-08 13:01:01(NULL)(NULL)9100390022021-09-08 14:01:01(NULL)(NULL)10100390032021-09-08 15:01:01(NULL)(NULL)11100590012021-09-01 12:01:012021-09-01 12:31:018812100590022021-09-01 12:01:012021-09-01 12:31:018813100590022021-09-02 12:11:012021-09-02 12:31:0189
iduidexam_idstart_timesubmit_timescore
id
uid
exam_id
start_time
submit_time
score
1100190012021-07-02 09:01:01(NULL)(NULL)
1
1001
9001
2021-07-02 09:01:01
(NULL)
(NULL)
2100290032021-09-01 12:01:012021-09-01 12:21:0160
2
1002
9003
2021-09-01 12:01:01
2021-09-01 12:21:01
60
3100290022021-09-02 12:01:012021-09-02 12:31:0170
3
1002
9002
2021-09-02 12:01:01
2021-09-02 12:31:01
70
4100290012021-09-05 19:01:012021-09-05 19:40:0181
4
1002
9001
2021-09-05 19:01:01
2021-09-05 19:40:01
81
5100290022021-07-06 12:01:01(NULL)(NULL)
5
1002
9002
2021-07-06 12:01:01
(NULL)
(NULL)
6100390032021-09-07 10:01:012021-09-07 10:31:0186
6
1003
9003
2021-09-07 10:01:01
2021-09-07 10:31:01
86
7100390032021-09-08 12:01:012021-09-08 12:11:0140
7
1003
9003
2021-09-08 12:01:01
2021-09-08 12:11:01
40
8100390012021-09-08 13:01:01(NULL)(NULL)
8
1003
9001
2021-09-08 13:01:01
(NULL)
(NULL)
9100390022021-09-08 14:01:01(NULL)(NULL)
9
1003
9002
2021-09-08 14:01:01
(NULL)
(NULL)
10100390032021-09-08 15:01:01(NULL)(NULL)
10
1003
9003
2021-09-08 15:01:01
(NULL)
(NULL)
11100590012021-09-01 12:01:012021-09-01 12:31:0188
11
1005
9001
2021-09-01 12:01:01
2021-09-01 12:31:01
88
12100590022021-09-01 12:01:012021-09-01 12:31:0188
12
1005
9002
2021-09-01 12:01:01
2021-09-01 12:31:01
88
13100590022021-09-02 12:11:012021-09-02 12:31:0189
13
1005
9002
2021-09-02 12:11:01
2021-09-02 12:31:01
89
试卷信息表 examination_info（exam_id：试卷 ID, tag：试卷类别, difficulty：试卷难度, duration：考试时长, release_time：发布时间），示例数据如下：
examination_infoexam_idtagdifficultydurationrelease_timeidexam_idtagdifficultydurationrelease_time19001SQLhard602020-01-01 10:00:0029002C++easy602020-02-01 10:00:0039003算法medium802020-08-02 10:00:00
idexam_idtagdifficultydurationrelease_time
id
exam_id
tag
difficulty
duration
release_time
19001SQLhard602020-01-01 10:00:00
1
9001
SQL
hard
60
2020-01-01 10:00:00
29002C++easy602020-02-01 10:00:00
2
9002
C++
easy
60
2020-02-01 10:00:00
39003算法medium802020-08-02 10:00:00
3
9003
算法
medium
80
2020-08-02 10:00:00
请从表中统计出 “当月均完成试卷数”不小于 3 的用户们爱作答的类别及作答次数，按次数降序输出，示例输出如下：
tagtag_cntC++4SQL2算法1
tagtag_cnt
tag
tag_cnt
C++4
C++
4
SQL2
SQL
2
算法1
算法
1
解释：用户 1002 和 1005 在 2021 年 09 月的完成试卷数目均为 3，其他用户均小于 3；然后用户 1002 和 1005 作答过的试卷 tag 分布结果按作答次数降序排序依次为 C++、SQL、算法。
思路：这题考察联合子查询，重点在于月均回答>=3, 但是个人认为这里没有表述清楚，应该直接说查 9 月的就容易理解多了；这里不是每个月都要>=3 或者是所有答题次数/答题月份。不要理解错误了。
月均回答>=3先查询出哪些用户月均答题大于三次
SELECT UID
FROM exam_record record
GROUP BY UID,
MONTH (start_time)
HAVING count(submit_time) >= 3SELECT UID
FROM exam_record record
GROUP BY UID,
MONTH (start_time)
HAVING count(submit_time) >= 3有了这一步之后再进行深入，只要能理解上一步(我的意思是不被题目中的月均所困扰)，然后再套一个子查询，查哪些用户包含其中，然后查出题目中所需的列即可。记得排序！！
SELECT tag,
count(start_time) AS tag_cnt
FROM exam_record record
INNER JOIN examination_info info ON record.exam_id = info.exam_id
WHERE UID IN
(SELECT UID
FROM exam_record record
GROUP BY UID,
MONTH (start_time)
HAVING count(submit_time) >= 3)
GROUP BY tag
ORDER BY tag_cnt DESCSELECT tag,
count(start_time) AS tag_cnt
FROM exam_record record
INNER JOIN examination_info info ON record.exam_id = info.exam_id
WHERE UID IN
(SELECT UID
FROM exam_record record
GROUP BY UID,
MONTH (start_time)
HAVING count(submit_time) >= 3)
GROUP BY tag
ORDER BY tag_cnt DESC试卷发布当天作答人数和平均分
描述：现有用户信息表 user_info（uid 用户 ID，nick_name 昵称, achievement 成就值, level 等级, job 职业方向, register_time 注册时间），示例数据如下：
user_infouidnick_nameachievementleveljobregister_timeiduidnick_nameachievementleveljobregister_time11001牛客 1 号31007算法2020-01-01 10:00:0021002牛客 2 号21006算法2020-01-01 10:00:0031003牛客 3 号15005算法2020-01-01 10:00:0041004牛客 4 号11004算法2020-01-01 10:00:0051005牛客 5 号16006C++2020-01-01 10:00:0061006牛客 6 号30006C++2020-01-01 10:00:00
iduidnick_nameachievementleveljobregister_time
id
uid
nick_name
achievement
level
job
register_time
11001牛客 1 号31007算法2020-01-01 10:00:00
1
1001
牛客 1 号
3100
7
算法
2020-01-01 10:00:00
21002牛客 2 号21006算法2020-01-01 10:00:00
2
1002
牛客 2 号
2100
6
算法
2020-01-01 10:00:00
31003牛客 3 号15005算法2020-01-01 10:00:00
3
1003
牛客 3 号
1500
5
算法
2020-01-01 10:00:00
41004牛客 4 号11004算法2020-01-01 10:00:00
4
1004
牛客 4 号
1100
4
算法
2020-01-01 10:00:00
51005牛客 5 号16006C++2020-01-01 10:00:00
5
1005
牛客 5 号
1600
6
C++
2020-01-01 10:00:00
61006牛客 6 号30006C++2020-01-01 10:00:00
6
1006
牛客 6 号
3000
6
C++
2020-01-01 10:00:00
释义：用户 1001 昵称为牛客 1 号，成就值为 3100，用户等级是 7 级，职业方向为算法，注册时间 2020-01-01 10:00:00
试卷信息表 examination_info（exam_id 试卷 ID, tag 试卷类别, difficulty 试卷难度, duration 考试时长, release_time 发布时间） 示例数据如下：
examination_infoexam_idtagdifficultydurationrelease_timeidexam_idtagdifficultydurationrelease_time19001SQLhard602021-09-01 06:00:0029002C++easy602020-02-01 10:00:0039003算法medium802020-08-02 10:00:00
idexam_idtagdifficultydurationrelease_time
id
exam_id
tag
difficulty
duration
release_time
19001SQLhard602021-09-01 06:00:00
1
9001
SQL
hard
60
2021-09-01 06:00:00
29002C++easy602020-02-01 10:00:00
2
9002
C++
easy
60
2020-02-01 10:00:00
39003算法medium802020-08-02 10:00:00
3
9003
算法
medium
80
2020-08-02 10:00:00
试卷作答记录表 exam_record（uid 用户 ID, exam_id 试卷 ID, start_time 开始作答时间, submit_time 交卷时间, score 得分） 示例数据如下：
exam_recorduidexam_idstart_timesubmit_timescoreiduidexam_idstart_timesubmit_timescore1100190012021-07-02 09:01:012021-09-01 09:41:01702100290032021-09-01 12:01:012021-09-01 12:21:01603100290022021-09-02 12:01:012021-09-02 12:31:01704100290012021-09-01 19:01:012021-09-01 19:40:01805100290032021-08-01 12:01:012021-08-01 12:21:01606100290022021-08-02 12:01:012021-08-02 12:31:01707100290012021-09-01 19:01:012021-09-01 19:40:01858100290022021-07-06 12:01:01(NULL)(NULL)9100390022021-09-07 10:01:012021-09-07 10:31:018610100390032021-09-08 12:01:012021-09-08 12:11:014011100390032021-09-01 13:01:012021-09-01 13:41:017012100390012021-09-08 14:01:01(NULL)(NULL)13100390022021-09-08 15:01:01(NULL)(NULL)14100590012021-09-01 12:01:012021-09-01 12:31:019015100590022021-09-01 12:01:012021-09-01 12:31:018816100590022021-09-02 12:11:012021-09-02 12:31:0189
iduidexam_idstart_timesubmit_timescore
id
uid
exam_id
start_time
submit_time
score
1100190012021-07-02 09:01:012021-09-01 09:41:0170
1
1001
9001
2021-07-02 09:01:01
2021-09-01 09:41:01
70
2100290032021-09-01 12:01:012021-09-01 12:21:0160
2
1002
9003
2021-09-01 12:01:01
2021-09-01 12:21:01
60
3100290022021-09-02 12:01:012021-09-02 12:31:0170
3
1002
9002
2021-09-02 12:01:01
2021-09-02 12:31:01
70
4100290012021-09-01 19:01:012021-09-01 19:40:0180
4
1002
9001
2021-09-01 19:01:01
2021-09-01 19:40:01
80
5100290032021-08-01 12:01:012021-08-01 12:21:0160
5
1002
9003
2021-08-01 12:01:01
2021-08-01 12:21:01
60
6100290022021-08-02 12:01:012021-08-02 12:31:0170
6
1002
9002
2021-08-02 12:01:01
2021-08-02 12:31:01
70
7100290012021-09-01 19:01:012021-09-01 19:40:0185
7
1002
9001
2021-09-01 19:01:01
2021-09-01 19:40:01
85
8100290022021-07-06 12:01:01(NULL)(NULL)
8
1002
9002
2021-07-06 12:01:01
(NULL)
(NULL)
9100390022021-09-07 10:01:012021-09-07 10:31:0186
9
1003
9002
2021-09-07 10:01:01
2021-09-07 10:31:01
86
10100390032021-09-08 12:01:012021-09-08 12:11:0140
10
1003
9003
2021-09-08 12:01:01
2021-09-08 12:11:01
40
11100390032021-09-01 13:01:012021-09-01 13:41:0170
11
1003
9003
2021-09-01 13:01:01
2021-09-01 13:41:01
70
12100390012021-09-08 14:01:01(NULL)(NULL)
12
1003
9001
2021-09-08 14:01:01
(NULL)
(NULL)
13100390022021-09-08 15:01:01(NULL)(NULL)
13
1003
9002
2021-09-08 15:01:01
(NULL)
(NULL)
14100590012021-09-01 12:01:012021-09-01 12:31:0190
14
1005
9001
2021-09-01 12:01:01
2021-09-01 12:31:01
90
15100590022021-09-01 12:01:012021-09-01 12:31:0188
15
1005
9002
2021-09-01 12:01:01
2021-09-01 12:31:01
88
16100590022021-09-02 12:11:012021-09-02 12:31:0189
16
1005
9002
2021-09-02 12:11:01
2021-09-02 12:31:01
89
请计算每张 SQL 类别试卷发布后，当天 5 级以上的用户作答的人数 uv 和平均分 avg_score，按人数降序，相同人数的按平均分升序，示例数据结果输出如下：
uvavg_scoreexam_iduvavg_score9001381.3
exam_iduvavg_score
exam_id
uv
avg_score
9001381.3
9001
3
81.3
解释：只有一张 SQL 类别的试卷，试卷 ID 为 9001，发布当天（2021-09-01）有 1001、1002、1003、1005 作答过，但是 1003 是 5 级用户，其他 3 位为 5 级以上，他们三的得分有[70,80,85,90]，平均分为 81.3（保留 1 位小数）。
思路：这题看似很复杂，但是先逐步将“外边”条件拆分，然后合拢到一起，答案就出来，多表查询反正记住：由外向里，抽丝剥茧。
先把三种表连起来，同时给定一些条件，比如题目中要求等级> 5的用户，那么可以先查出来
等级> 5SELECT DISTINCT u_info.uid
FROM examination_info e_info
INNER JOIN exam_record record
INNER JOIN user_info u_info
WHERE e_info.exam_id = record.exam_id
AND u_info.uid = record.uid
AND u_info.LEVEL > 5SELECT DISTINCT u_info.uid
FROM examination_info e_info
INNER JOIN exam_record record
INNER JOIN user_info u_info
WHERE e_info.exam_id = record.exam_id
AND u_info.uid = record.uid
AND u_info.LEVEL > 5接着注意题目中要求：每张sql类别试卷发布后，当天作答用户，注意其中的==当天==，那我们马上就要想到要用到时间的比较。
每张sql类别试卷发布后，当天作答用户对试卷发布日期和开始考试日期进行比较：DATE(e_info.release_time) = DATE(record.start_time)；不用担心submit_time 为 null 的问题，后续在 where 中会给过滤掉。
DATE(e_info.release_time) = DATE(record.start_time)submit_time答案：
SELECT record.exam_id AS exam_id,
COUNT(DISTINCT u_info.uid) AS uv,
ROUND(SUM(record.score) / COUNT(u_info.uid), 1) AS avg_score
FROM examination_info e_info
INNER JOIN exam_record record
INNER JOIN user_info u_info
WHERE e_info.exam_id = record.exam_id
AND u_info.uid = record.uid
AND DATE (e_info.release_time) = DATE (record.start_time)
AND submit_time IS NOT NULL
AND tag = 'SQL'
AND u_info.LEVEL > 5
GROUP BY record.exam_id
ORDER BY uv DESC,
avg_score ASCSELECT record.exam_id AS exam_id,
COUNT(DISTINCT u_info.uid) AS uv,
ROUND(SUM(record.score) / COUNT(u_info.uid), 1) AS avg_score
FROM examination_info e_info
INNER JOIN exam_record record
INNER JOIN user_info u_info
WHERE e_info.exam_id = record.exam_id
AND u_info.uid = record.uid
AND DATE (e_info.release_time) = DATE (record.start_time)
AND submit_time IS NOT NULL
AND tag = 'SQL'
AND u_info.LEVEL > 5
GROUP BY record.exam_id
ORDER BY uv DESC,
avg_score ASC注意最后的分组排序！先按人数排，若一致，按平均分排。
作答试卷得分大于过 80 的人的用户等级分布
描述：
现有用户信息表 user_info（uid 用户 ID，nick_name 昵称, achievement 成就值, level 等级, job 职业方向, register_time 注册时间）：
user_infouidnick_nameachievementleveljobregister_timeiduidnick_nameachievementleveljobregister_time11001牛客 1 号31007算法2020-01-01 10:00:0021002牛客 2 号21006算法2020-01-01 10:00:0031003牛客 3 号15005算法2020-01-01 10:00:0041004牛客 4 号11004算法2020-01-01 10:00:0051005牛客 5 号16006C++2020-01-01 10:00:0061006牛客 6 号30006C++2020-01-01 10:00:00
iduidnick_nameachievementleveljobregister_time
id
uid
nick_name
achievement
level
job
register_time
11001牛客 1 号31007算法2020-01-01 10:00:00
1
1001
牛客 1 号
3100
7
算法
2020-01-01 10:00:00
21002牛客 2 号21006算法2020-01-01 10:00:00
2
1002
牛客 2 号
2100
6
算法
2020-01-01 10:00:00
31003牛客 3 号15005算法2020-01-01 10:00:00
3
1003
牛客 3 号
1500
5
算法
2020-01-01 10:00:00
41004牛客 4 号11004算法2020-01-01 10:00:00
4
1004
牛客 4 号
1100
4
算法
2020-01-01 10:00:00
51005牛客 5 号16006C++2020-01-01 10:00:00
5
1005
牛客 5 号
1600
6
C++
2020-01-01 10:00:00
61006牛客 6 号30006C++2020-01-01 10:00:00
6
1006
牛客 6 号
3000
6
C++
2020-01-01 10:00:00
试卷信息表 examination_info（exam_id 试卷 ID, tag 试卷类别, difficulty 试卷难度, duration 考试时长, release_time 发布时间）：
examination_infoexam_idtagdifficultydurationrelease_timeidexam_idtagdifficultydurationrelease_time19001SQLhard602021-09-01 06:00:0029002C++easy602021-09-01 06:00:0039003算法medium802021-09-01 10:00:00
idexam_idtagdifficultydurationrelease_time
id
exam_id
tag
difficulty
duration
release_time
19001SQLhard602021-09-01 06:00:00
1
9001
SQL
hard
60
2021-09-01 06:00:00
29002C++easy602021-09-01 06:00:00
2
9002
C++
easy
60
2021-09-01 06:00:00
39003算法medium802021-09-01 10:00:00
3
9003
算法
medium
80
2021-09-01 10:00:00
试卷作答信息表 exam_record（uid 用户 ID, exam_id 试卷 ID, start_time 开始作答时间, submit_time 交卷时间, score 得分）：
exam_recorduidexam_idstart_timesubmit_timescoreiduidexam_idstart_timesubmit_timescore1100190012021-09-01 09:01:012021-09-01 09:41:01792100290032021-09-01 12:01:012021-09-01 12:21:01603100290022021-09-01 12:01:012021-09-01 12:31:01704100290012021-09-01 19:01:012021-09-01 19:40:01805100290032021-08-01 12:01:012021-08-01 12:21:01606100290022021-09-01 12:01:012021-09-01 12:31:01707100290012021-09-01 19:01:012021-09-01 19:40:01858100290022021-09-01 12:01:01(NULL)(NULL)9100390022021-09-07 10:01:012021-09-07 10:31:018610100390032021-09-08 12:01:012021-09-08 12:11:014011100390032021-09-01 13:01:012021-09-01 13:41:018112100390012021-09-01 14:01:01(NULL)(NULL)13100390022021-09-08 15:01:01(NULL)(NULL)14100590012021-09-01 12:01:012021-09-01 12:31:019015100590022021-09-01 12:01:012021-09-01 12:31:018816100590022021-09-02 12:11:012021-09-02 12:31:0189
iduidexam_idstart_timesubmit_timescore
id
uid
exam_id
start_time
submit_time
score
1100190012021-09-01 09:01:012021-09-01 09:41:0179
1
1001
9001
2021-09-01 09:01:01
2021-09-01 09:41:01
79
2100290032021-09-01 12:01:012021-09-01 12:21:0160
2
1002
9003
2021-09-01 12:01:01
2021-09-01 12:21:01
60
3100290022021-09-01 12:01:012021-09-01 12:31:0170
3
1002
9002
2021-09-01 12:01:01
2021-09-01 12:31:01
70
4100290012021-09-01 19:01:012021-09-01 19:40:0180
4
1002
9001
2021-09-01 19:01:01
2021-09-01 19:40:01
80
5100290032021-08-01 12:01:012021-08-01 12:21:0160
5
1002
9003
2021-08-01 12:01:01
2021-08-01 12:21:01
60
6100290022021-09-01 12:01:012021-09-01 12:31:0170
6
1002
9002
2021-09-01 12:01:01
2021-09-01 12:31:01
70
7100290012021-09-01 19:01:012021-09-01 19:40:0185
7
1002
9001
2021-09-01 19:01:01
2021-09-01 19:40:01
85
8100290022021-09-01 12:01:01(NULL)(NULL)
8
1002
9002
2021-09-01 12:01:01
(NULL)
(NULL)
9100390022021-09-07 10:01:012021-09-07 10:31:0186
9
1003
9002
2021-09-07 10:01:01
2021-09-07 10:31:01
86
10100390032021-09-08 12:01:012021-09-08 12:11:0140
10
1003
9003
2021-09-08 12:01:01
2021-09-08 12:11:01
40
11100390032021-09-01 13:01:012021-09-01 13:41:0181
11
1003
9003
2021-09-01 13:01:01
2021-09-01 13:41:01
81
12100390012021-09-01 14:01:01(NULL)(NULL)
12
1003
9001
2021-09-01 14:01:01
(NULL)
(NULL)
13100390022021-09-08 15:01:01(NULL)(NULL)
13
1003
9002
2021-09-08 15:01:01
(NULL)
(NULL)
14100590012021-09-01 12:01:012021-09-01 12:31:0190
14
1005
9001
2021-09-01 12:01:01
2021-09-01 12:31:01
90
15100590022021-09-01 12:01:012021-09-01 12:31:0188
15
1005
9002
2021-09-01 12:01:01
2021-09-01 12:31:01
88
16100590022021-09-02 12:11:012021-09-02 12:31:0189
16
1005
9002
2021-09-02 12:11:01
2021-09-02 12:31:01
89
统计作答 SQL 类别的试卷得分大于过 80 的人的用户等级分布，按数量降序排序（保证数量都不同）。示例数据结果输出如下：
levellevel_cnt6251
levellevel_cnt
level
level_cnt
62
6
2
51
5
1
解释：9001 为 SQL 类试卷，作答该试卷大于 80 分的人有 1002、1003、1005 共 3 人，6 级两人，5 级一人。
思路：这题和上一题都是一样的数据，只是查询条件改变了而已，上一题理解了，这题分分钟做出来。
答案：
SELECT u_info.LEVEL AS LEVEL,
count(u_info.uid) AS level_cnt
FROM examination_info e_info
INNER JOIN exam_record record
INNER JOIN user_info u_info
WHERE e_info.exam_id = record.exam_id
AND u_info.uid = record.uid
AND record.score > 80
AND submit_time IS NOT NULL
AND tag = 'SQL'
GROUP BY LEVEL
ORDER BY level_cnt DESCSELECT u_info.LEVEL AS LEVEL,
count(u_info.uid) AS level_cnt
FROM examination_info e_info
INNE