文章来源于网络收集而来，版权归原创者所有，如有侵权请及时联系！

嵌套子查询

发布于 2024-08-17 15:49:00 字数 14479 浏览 0 评论 0 收藏 0

月均完成试卷数不小于 3 的用户爱作答的类别（较难）

描述：现有试卷作答记录表 exam_record （ uid ：用户 ID, exam_id ：试卷 ID, start_time ：开始作答时间, submit_time ：交卷时间，没提交的话为 NULL, score ：得分），示例数据如下：

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

试卷信息表 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	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、算法。

思路：这题考察联合子查询，重点在于 月均回答>=3 , 但是个人认为这里没有表述清楚，应该直接说查 9 月的就容易理解多了；这里不是每个月都要>=3 或者是所有答题次数/答题月份。不要理解错误了。

先查询出哪些用户月均答题大于三次

SELECT 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 DESC

试卷发布当天作答人数和平均分

描述：现有用户信息表 user_info （ uid 用户 ID， nick_name 昵称, achievement 成就值, level 等级, job 职业方向, register_time 注册时间），示例数据如下：

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

试卷信息表 examination_info （ exam_id 试卷 ID, tag 试卷类别, difficulty 试卷难度, duration 考试时长, release_time 发布时间）示例数据如下：

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

试卷作答记录表 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-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 级以上的用户作答的人数 uv 和平均分 avg_score ，按人数降序，相同人数的按平均分升序，示例数据结果输出如下：

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 位小数）。

思路：这题看似很复杂，但是先逐步将“外边”条件拆分，然后合拢到一起，答案就出来，多表查询反正记住：由外向里，抽丝剥茧。

先把三种表连起来，同时给定一些条件，比如题目中要求 等级> 5 的用户，那么可以先查出来

SELECT 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 类别试卷发布后，当天作答用户 ，注意其中的==当天==，那我们马上就要想到要用到时间的比较。

对试卷发布日期和开始考试日期进行比较： DATE(e_info.release_time) = DATE(record.start_time) ；不用担心 submit_time 为 null 的问题，后续在 where 中会给过滤掉。

答案：

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 ASC

注意最后的分组排序！先按人数排，若一致，按平均分排。

作答试卷得分大于过 80 的人的用户等级分布

描述：

现有用户信息表 user_info （ uid 用户 ID， nick_name 昵称, achievement 成就值, level 等级, job 职业方向, register_time 注册时间）：

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

试卷信息表 examination_info （ exam_id 试卷 ID, tag 试卷类别, difficulty 试卷难度, duration 考试时长, release_time 发布时间）：

id	exam_id	tag	difficulty	duration	release_time
1	9001	SQL	hard	60	2021-09-01 06:00:00
2	9002	C++	easy	60	2021-09-01 06:00:00
3	9003	算法	medium	80	2021-09-01 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	2021-09-01 09:01:01	2021-09-01 09:41:01	79
2	1002	9003	2021-09-01 12:01:01	2021-09-01 12:21:01	60
3	1002	9002	2021-09-01 12:01:01	2021-09-01 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-09-01 12:01:01	2021-09-01 12:31:01	70
7	1002	9001	2021-09-01 19:01:01	2021-09-01 19:40:01	85
8	1002	9002	2021-09-01 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	81
12	1003	9001	2021-09-01 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 类别的试卷得分大于过 80 的人的用户等级分布，按数量降序排序（保证数量都不同）。示例数据结果输出如下：

level	level_cnt
6	2
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 DESC

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

列表为空，暂无数据