SQL123 SQL类别高难度试卷得分的截断平均值

携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第12天，点击查看活动详情

❤️‍欢迎订阅java厂长《SQL每日一题》 ❤️‍

1、题目📑

牛客的运营同学想要查看大家在SQL类别中高难度试卷的得分情况。

请你帮她从exam_record数据表中计算所有用户完成SQL类别高难度试卷得分的截断平均值（去掉一个最大值和一个最小值后的平均值）。

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

示例：用户信息表user_info

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

从examination_info表可知，试卷9001为高难度SQL试卷，该试卷被作答的得分有[80,81,84,90,50]，去除最高分和最低分后为[80,81,84]，平均分为81.6666667，保留一位小数后为81.7

输入描述：

输入数据中至少有3个有效分数

示例1

输入：
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);

输出：
SQL|hard|81.7

2、思路🧠

细节剖析：

筛选SQL高难度试卷：where tag = 'SQL' and difficulty='hard'
计算截断平均值：(和-最大值-最小值) / (总个数-2): (sum(score) - max(score) - min(score)) / (count(score) - 2)

细节问题：

表头重命名：as
保留1位小数：round(..., 1)

3、代码👨‍💻

commit AC

SELECT
	t1.tag,
	t1.difficulty,
	t2.total_score 
FROM
	( SELECT tag, difficulty, exam_id FROM examination_info WHERE tag = 'SQL' AND difficulty = 'hard' ) AS t1
	INNER JOIN (
	SELECT
		exam_id,
		ROUND(( sum( score ) - max( score ) - min( score ))/( count( score ) - 2 ), 1 ) AS total_score 
	FROM
		exam_record 
	WHERE
		score IS NOT NULL 
	GROUP BY
	exam_id 
	) AS t2 ON t1.exam_id = t2.exam_id

4、总结

该题目的对SQL的语法及基础知识，学会统计分数等进行函数使用等，去除最大值和最小值统计分数或者得分有很多的解法，要合理选择解决办法，像内连接、外连接、左连接、右连接等都要有相关的了解，其次当你编写了大量的SQL之后，就要学会进行SQL的优化，这对于数据查询的时间会有大幅度的降低。

❤️‍来自专栏《Mysql每日一题》欢迎订阅❤️‍

厂长写博客目的初衷很简单，希望大家在学习的过程中少走弯路，多学一些东西，对自己有帮助的留下你的赞赞👍或者关注➕都是对我最大的支持，你的关注和点赞给厂长每天更文的动力。

对文章其中一部分不理解，都可以评论区回复我，我们来一起讨论，共同学习，一起进步！

原题链接：SQL123 SQL类别高难度试卷得分的截断平均值（牛客网）