SQL常见面试题总结（3）

题目来源于：牛客题霸 SQL 进阶挑战 较难或者困难的题目可以根据自身实际情况和面试需要来决定是否要跳过。 聚合函数 SQL 类别高难度试卷得分的截断平均值（较难） 描述 ： 牛客的运营同学想要查看大家在 SQL 类别中高难度试卷的得分情况。 请你帮她从 数据表中计算所有用户完成 SQL 类别高难度试卷得分的截断平均值（去掉一个最大值和一个最小值后的平均值）。 示例数据： （ 试卷 ID, tag 试卷类别, 试卷难度, 考试时长, 发布时间） | 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 | 示例数据： （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 09 07 12:01:01 | 2021 09 07 10:31:01 | 50 | | 10 | 1004 | 9001 | 2021 09 06 10:01:01 | (NULL) | (NULL) | 根据输入你的查询结果如下： | tag | difficulty | clip\ avg\ score | | | | | | SQL | hard | 81.7 | 从 表可知，试卷 9001 为高难度 SQL 试卷，该试卷被作答的得分有[80,81,84,90,50]，去除最高分和最低分后为[80,81,84]，平均分为 81.6666667，保留一位小数后为 81.7 输入描述： 输入数据中至少有 3 个有效分数 思路一： 要找出高难度 sql 试卷，肯定需要联 examination\ info 这张表，然后找出高难度的课程，由 examination\ info 得知，高难度 sql 的 exam\ id 为 9001，那么等下就以 exam\ id = 9001 作为条件去查询； 先找出 9001 号考试 然后，找出最高分 接着，找出最低分 在查询出来的分数结果集当中，去掉最高分和最低分，最直观能想到的就是 NOT IN 或者 用 NOT EXISTS 也行，这里以 NOT IN 来做 首先将主体写出来 小 tips : MYSQL 的 函数 , 返回参数 X 最近似的整数 返回 X ,其值保留到小数点后 D 位,第 D 位的保留方式为四舍五入。 再将上面的 "碎片" 语句拼凑起来即可， 注意在 NOT IN 中两个子查询用 UNION ALL 来关联，用 union 把 max 和 min 的结果集中在一行当中，这样形成一列多行的效果。 答案一： 这是最直观，也是最容易想到的解法，但是还有待改进，这算是投机取巧过关，其实严格按照题目要求应该这么写： 然而你会发现，重复的语句非常多，所以可以利用 来抽取公共部分 子句介绍 ： 子句，也称为公共表表达式（Common Table Expression，CTE），是在 SQL 查询中定义临时表的方式。它可以让我们在查询中创建一个临时命名的结果集，并且可以在同一查询中引用该结果集。 基本用法： 子句由以下几个部分组成： : 给临时表起一个名称，可以在主查询中引用。 : 可选，指定临时表的列名。 : 必需，表示开始定义临时表。 : 实际的查询语句，用于定义临时表中的数据。 子句的主要用途之一是增强查询的可读性和可维护性，尤其在涉及多个嵌套子查询或需要重复使用相同的查询逻辑时。通过将这些逻辑放在一个命名的临时表中，我们可以更清晰地组织查询，并消除重复代码。 此外， 子句还可以在复杂的查询中实现递归查询。递归查询允许我们在单个查询中执行对同一表的多次迭代，逐步构建结果集。这在处理层次结构数据、组织结构和树状结构等场景中非常有用。 小细节 ：MySQL 5.7 版本以及之前的版本不支持在 子句中直接使用别名。 下面是改进后的答案： 思路二： 筛选 SQL 高难度试卷： 计算截断平均值： : 有一个缺点就是，如果最大值和最小值有多个，这个方法就很难筛选出来, 但是题目中说了 , 所以这里可以用这个公式。 答案二： 统计作答次数 有一个试卷作答记录表 ，请从中统计出总作答次数 、试卷已完成作答数 、已完成的试卷数 。 示例数据 表（ 用户 ID, 试卷 ID, 开始作答时间, 交卷时间, 得分）： | 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 09 07 12:01:01 | 2021 09 07 10:31:01 | 50 | | 10 | 1004 | 9001 | 2021 09 06 10:01:01 | (NULL) | (NULL) | 示例输出： | total\ pv | complete\ pv | complete\ exam\ cnt | | | | | | 10 | 7 | 2 | 解释：表示截止当前，有 10 次试卷作答记录，已完成的作答次数为 7 次（中途退出的为未完成状态，其交卷时间和份数为 NULL），已完成的试卷有 9001 和 9002 两份。 思路 ： 这题一看到统计次数，肯定第一时间就要想到用 这个函数来解决，问题是要统计不同的记录，该怎么来写？使用子查询就能解决这个题目(这题用 case when 也能写出来，解法类似，逻辑不同而已)；首先在做这个题之前，让我们先来了解一下 的基本用法； 函数的基本语法如下所示： 其中， 可以是列名、表达式、常量或通配符。下面是一些常见的用法示例： 1. 计算表中所有行的数量： 2. 计算特定列非空（不为 NULL）值的数量： 3. 计算满足条件的行数： 4. 结合 使用，计算分组后每个组的行数： 5. 计算不同列组合的唯一组合数： 在使用 函数时，如果不指定任何参数或者使用 ，将会计算所有行的数量。而如果使用列名，则只会计算该列非空值的数量。 另外， 函数的结果是一个整数值。即使结果是零，也不会返回 NULL，这点需要谨记。 答案 ： 这里着重说一下 这一句，判断 score 是否为 null ，如果是即为真，如果不是返回 null；注意这里如果不加 在不是 null 的情况下只会返回 false 也就是返回 0； 本身是不可以对多列求行数的， 的加入是的多列成为一个整体，可以求出现的行数了; 在计算时只返回非 null 的行, 这个也要注意； 另外通过本题 get 到了 count 加条件常用句式 得分不小于平均分的最低分 描述 ： 请从试卷作答记录表中找到 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) | 表（ 试卷 ID, 试卷类别, 试卷难度, 考试时长, 发布时间） | 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 | 解释 ：试卷 9001 和 9002 为 SQL 类别，作答这两份试卷的得分有[80,89,87,90]，平均分为 86.5，不小于平均分的最小分数为 87 思路 ：这类题目第一眼看确实很复杂， 因为不知道从哪入手，但是当我们仔细读题审题后，要学会抓住题干中的关键信息。以本题为例： 你能一眼从中提取哪些有效信息来作为解题思路？ 第一条：找到==SQL==试卷得分 第二条：该类试卷==平均得分== 第三条：该类试卷的==用户最低得分== 然后中间的 “桥梁” 就是==不小于== 将条件拆分后，先逐步完成 然后再找出该类试卷的最低得分，接着将结果集 去和平均分数作比较，方可得出最终答案。 答案 ： 其实这类题目给出的要求看似很 “绕”，但其实仔细梳理一遍，将大条件拆分成小条件，逐个拆分完以后，最后将所有条件拼凑起来。反正只要记住： 抓主干，理分支 ，问题便迎刃而解。 分组查询 平均活跃天数和月活人数 描述 ：用户在牛客试卷作答区作答记录存储在表 中，内容如下： 表（ 用户 ID, 试卷 ID, 开始作答时间, 交卷时间, 得分） | 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 年每个月里试卷作答区用户平均月活跃天数 和月度活跃人数 ，上面数据的示例输出如下： | 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 位小数。 注：此处活跃指有==交卷==行为。 思路 ：读完题先注意高亮部分；一般求天数和月活跃人数马上就要想到相关的日期函数；这一题我们同样来进行拆分，把问题细化再解决；首先求活跃人数，肯定要用到 ，那这里首先就有一个坑，不知道大家注意了没有？用户 1002 在 9 月份做了两种不同的试卷，所以这里要注意去重，不然在统计的时候，活跃人数是错的；第二个就是要知道日期的格式化，如上表，题目要求以 这种日期格式展现，要用到 来进行格式化。 基本用法： 参数是待格式化的日期或时间值。 参数是指定的日期或时间格式（这个和 Java 里面的日期格式一样）。 答案 ： 这里多说一句, 使用 可以统计在 列和 列按照年份、月份和日期进行格式化后的组合值的数量。 月总刷题数和日均刷题数 描述 ：现有一张题目练习记录表 ，示例内容如下： | 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 年每个月里用户的月总刷题数 和日均刷题数 （按月份升序排序）以及该年的总体情况，示例数据输出如下： | 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） 牛客已经采用最新的 Mysql 版本，如果您运行结果出现错误：ONLY\ FULL\ GROUP\ BY，意思是：对于 GROUP BY 聚合操作，如果在 SELECT 中的列，没有在 GROUP BY 中出现，那么这个 SQL 是不合法的，因为列不在 GROUP BY 从句中，也就是说查出来的列必须在 group by 后面出现否则就会报错，或者这个字段出现在聚合函数里面。 思路： 看到实例数据就要马上联想到相关的函数，比如 就要用到 来格式化日期。然后查出每月的刷题数量。 每月的刷题数量 接着第三列这里要用到 函数来查找给定日期的月份中的天数。 示例代码如下： 使用 函数获取给定日期的当月最后一天，然后使用 函数提取该日期的天数。这样就能获得指定月份的天数。 需要注意的是， 函数返回的是日期值，而 函数用于提取日期值中的天数部分。 有了上述的分析之后，即可马上写出答案，这题复杂就复杂在处理日期上，其中的逻辑并不难。 答案 ： 在实例数据输出中因为最后一行需要得出汇总数据，所以这里要 加到结果集中；别忘了最后要排序！ 未完成试卷数大于 1 的有效用户（较难） 描述 ：现有试卷作答记录表 （ 用户 ID, 试卷 ID, 开始作答时间, 交卷时间, 得分），示例数据如下： | 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 | 还有一张试卷信息表 （ 试卷 ID, 试卷类别, 试卷难度, 考试时长, 发布时间），示例数据如下： | id | exam\ id | tag | difficulty | duration | release\ time | | | | | | | | | 1 | 9001 | SQL | hard | 60 | 2020 01 01 10:00:00 | | 2 | 9002 | SQL | easy | 60 | 2020 02 01 10:00:00 | | 3 | 9003 | 算法 | medium | 80 | 2020 08 02 10:00:00 | 请统计 2021 年每个未完成试卷作答数大于 1 的有效用户的数据（有效用户指完成试卷作答数至少为 1 且未完成数小于 5），输出用户 ID、未完成试卷作答数、完成试卷作答数、作答过的试卷 tag 集合，按未完成试卷数量由多到少排序。示例数据的输出结果如下： | uid | incomplete\ cnt | complete\ cnt | detail | | | | | | | 1002 | 2 | 4 | 2021 09 01:算法;2021 07 02:SQL;2021 09 02:SQL;2021 09 05:SQL;2021 07 05:SQL | 解释 ：2021 年的作答记录中，除了 1004，其他用户均满足有效用户定义，但只有 1002 未完成试卷数大于 1，因此只输出 1002，detail 中是 1002 作答过的试卷{日期:tag}集合，日期和 tag 间用 : 连接，多元素间用 ; 连接。 思路： 仔细读题后，分析出：首先要联表，因为后面要输出 ； 筛选出 2021 年的数据 根据 uid 进行分组，然后对每个用户进行条件进行判断，题目中要求 那么等会儿写 sql 的时候条件应该是： 因为最后要用到字符串的拼接，而且还要组合拼接，这个可以用 函数，下面简单介绍一下该函数的用法： 基本格式： ：要连接的列或表达式。 ：可选参数，用于去重。当指定了 ，相同的值只会出现一次。 ：可选参数，用于排序连接后的值。可以选择升序 ( ) 或降序 ( ) 排序。 ：可选参数，用于设置连接后的值的分隔符。（本题要用这个参数设置 ; 号 ） 函数常用于 子句中，将一组行的值连接为一个字符串，并在结果集中以聚合的形式返回。 答案 ： 统计了每个用户未完成的记录数量。 统计了每个用户已完成的记录数量。 将每个用户的考试日期和标签以逗号分隔的形式连接成一个字符串，并按考试开始时间进行排序。 嵌套子查询 月均完成试卷数不小于 3 的用户爱作答的类别（较难） 描述 ：现有试卷作答记录表 （ ：用户 ID, ：试卷 ID, ：开始作答时间, ：交卷时间，没提交的话为 NULL, ：得分），示例数据如下： | id | uid | exam\ id | start\ time | submit\ time | score | | | | | | | | | 1 | 1001 | 9001 | 2021 07 02 09:01:01 | (NULL) | (NULL) | | 2 | 1002 | 9003 | 2021 09 01 12:01:01 | 2021 09 01 12:21:01 | 60 | | 3 | 1002 | 9002 | 2021 09 02 12:01:01 | 2021 09 02 12:31:01 | 70 | | 4 | 1002 | 9001 | 2021 09 05 19:01:01 | 2021 09 05 19:40:01 | 81 | | 5 | 1002 | 9002 | 2021 07 06 12:01:01 | (NULL) | (NULL) | | 6 | 1003 | 9003 | 2021 09 07 10:01:01 | 2021 09 07 10:31:01 | 86 | | 7 | 1003 | 9003 | 2021 09 08 12:01:01 | 2021 09 08 12:11:01 | 40 | | 8 | 1003 | 9001 | 2021 09 08 13:01:01 | (NULL) | (NULL) | | 9 | 1003 | 9002 | 2021 09 08 14:01:01 | (NULL) | (NULL) | | 10 | 1003 | 9003 | 2021 09 08 15:01:01 | (NULL) | (NULL) | | 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 | 1005 | 9002 | 2021 09 02 12:11:01 | 2021 09 02 12:31:01 | 89 | 试卷信息表 （ ：试卷 ID, ：试卷类别, ：试卷难度, ：考试时长, ：发布时间），示例数据如下： | id | exam\ id | tag | difficulty | duration | release\ time | | | | | | | | | 1 | 9001 | SQL | hard | 60 | 2020 01 01 10:00:00 | | 2 | 9002 | C++ | easy | 60 | 2020 02 01 10:00:00 | | 3 | 9003 | 算法 | medium | 80 | 2020 08 02 10:00:00 | 请从表中统计出 “当月均完成试卷数”不小于 3 的用户们爱作答的类别及作答次数，按次数降序输出，示例输出如下： | tag | tag\ cnt | | | | | C++ | 4 | | SQL | 2 | | 算法 | 1 | 解释 ：用户 1002 和 1005 在 2021 年 09 月的完成试卷数目均为 3，其他用户均小于 3；然后用户 1002 和 1005 作答过的试卷 tag 分布结果按作答次数降序排序依次为 C++、SQL、算法。 思路 ：这题考察联合子查询，重点在于 , 但是个人认为这里没有表述清楚，应该直接说查 9 月的就容易理解多了；这里不是每个月都要 =3 或者是所有答题次数/答题月份。不要理解错误了。 先查询出哪些用户月均答题大于三次 有了这一步之后再进行深入，只要能理解上一步(我的意思是不被题目中的月均所困扰)，然后再套一个子查询，查哪些用户包含其中，然后查出题目中所需的列即可。记得排序！！ 试卷发布当天作答人数和平均分 描述 ：现有用户信息表 （ 用户 ID， 昵称, 成就值, 等级, 职业方向, 注册时间），示例数据如下： | id | uid | nick\ name | achievement | level | job | register\ time | | | | | | | | | | 1 | 1001 | 牛客 1 号 | 3100 | 7 | 算法 | 2020 01 01 10:00:00 | | 2 | 1002 | 牛客 2 号 | 2100 | 6 | 算法 | 2020 01 01 10:00:00 | | 3 | 1003 | 牛客 3 号 | 1500 | 5 | 算法 | 2020 01 01 10:00:00 | | 4 | 1004 | 牛客 4 号 | 1100 | 4 | 算法 | 2020 01 01 10:00:00 | | 5 | 1005 | 牛客 5 号 | 1600 | 6 | C++ | 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 试卷信息表 （ 试卷 ID, 试卷类别, 试卷难度, 考试时长, 发布时间） 示例数据如下： | id | exam\ id | tag | difficulty | duration | release\ time | | | | | | | | | 1 | 9001 | SQL | hard | 60 | 2021 09 01 06:00:00 | | 2 | 9002 | C++ | easy | 60 | 2020 02 01 10:00:00 | | 3 | 9003 | 算法 | medium | 80 | 2020 08 02 10:00:00 | 试卷作答记录表 （ 用户 ID, 试卷 ID, 开始作答时间, 交卷时间, 得分） 示例数据如下： | id | uid | exam\ id | start\ time | submit\ time | score | | | | | | | | | 1 | 1001 | 9001 | 2021 07 02 09:01:01 | 2021 09 01 09:41:01 | 70 | | 2 | 1002 | 9003 | 2021 09 01 12:01:01 | 2021 09 01 12:21:01 | 60 | | 3 | 1002 | 9002 | 2021 09 02 12:01:01 | 2021 09 02 12:31:01 | 70 | | 4 | 1002 | 9001 | 2021 09 01 19:01:01 | 2021 09 01 19:40:01 | 80 | | 5 | 1002 | 9003 | 2021 08 01 12:01:01 | 2021 08 01 12:21:01 | 60 | | 6 | 1002 | 9002 | 2021 08 02 12:01:01 | 2021 08 02 12:31:01 | 70 | | 7 | 1002 | 9001 | 2021 09 01 19:01:01 | 2021 09 01 19:40:01 | 85 | | 8 | 1002 | 9002 | 2021 07 06 12:01:01 | (NULL) | (NULL) | | 9 | 1003 | 9002 | 2021 09 07 10:01:01 | 2021 09 07 10:31:01 | 86 | | 10 | 1003 | 9003 | 2021 09 08 12:01:01 | 2021 09 08 12:11:01 | 40 | | 11 | 1003 | 9003 | 2021 09 01 13:01:01 | 2021 09 01 13:41:01 | 70 | | 12 | 1003 | 9001 | 2021 09 08 14:01:01 | (NULL) | (NULL) | | 13 | 1003 | 9002 | 2021 09 08 15:01:01 | (NULL) | (NULL) | | 14 | 1005 | 9001 | 2021 09 01 12:01:01 | 2021 09 01 12:31:01 | 90 | | 15 | 1005 | 9002 | 2021 09 01 12:01:01 | 2021 09 01 12:31:01 | 88 | | 16 | 1005 | 9002 | 2021 09 02 12:11:01 | 2021 09 02 12:31:01 | 89 | 请计算每张 SQL 类别试卷发布后，当天 5 级以上的用户作答的人数 和平均分 ，按人数降序，相同人数的按平均分升序，示例数据结果输出如下： | exam\ id | uv | avg\ score | | | | | | 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 位小数）。 思路 ：这题看似很复杂，但是先逐步将“外边”条件拆分，然后合拢到一起，答案就出来，多表查询反正记住：由外向里，抽丝剥茧。 先把三种表连起来，同时给定一些条件，比如题目中要求 的用户，那么可以先查出来 接着注意题目中要求： ，注意其中的==当天==，那我们马上就要想到要用到时间的比较。 对试卷发布日期和开始考试日期进行比较： ；不用担心 为 null 的问题，后续在 where 中会给过滤掉。 答案 ： 注意最后的分组排序！先按人数排，若一致，按平均分排。 作答试卷得分大于过 80 的人的用户等级分布 描述 ： 现有用户信息表 （ 用户 ID， 昵称, 成就值, 等级, 职业方向, 注册时间）： | id | uid | nick\ name | achievement | level | job | register\ time | | | | | | | | | | 1 | 1001 | 牛客 1 号 | 3100 | 7 | 算法 | 2020 01 01 10:00:00 | | 2 | 1002 | 牛客 2 号 | 2