题目

image.png
image.png

示例

  1. drop table if exists examination_info;
  2. CREATE TABLE  examination_info (
  3.     id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
  4.     exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
  5.     tag varchar(32) COMMENT '类别标签',
  6.     difficulty varchar(8) COMMENT '难度',
  7.     duration int NOT NULL COMMENT '时长',
  8.     release_time datetime COMMENT '发布时间'
  9. )CHARACTER SET utf8 COLLATE utf8_general_ci;
  11. drop table if exists exam_record;
  12. CREATE TABLE exam_record (
  13.     id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
  14.     uid int NOT NULL COMMENT '用户ID',
  15.     exam_id int NOT NULL COMMENT '试卷ID',
  16.     start_time datetime NOT NULL COMMENT '开始时间',
  17.     submit_time datetime COMMENT '提交时间',
  18.     score tinyint COMMENT '得分'
  19. )CHARACTER SET utf8 COLLATE utf8_general_ci;
  21. INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  22.   (9001, 'SQL', 'hard', 60, '2020-01-01 10:00:00'),
  23.   (9002, '算法', 'medium', 80, '2020-08-02 10:00:00');
  25. INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
  26. (1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:01', 80),
  27. (1001, 9001, '2021-05-02 10:01:01', '2021-05-02 10:30:01', 81),
  28. (1001, 9001, '2021-06-02 19:01:01', '2021-06-02 19:31:01', 84),
  29. (1001, 9002, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89),
  30. (1001, 9001, '2021-09-02 12:01:01', null, null),
  31. (1001, 9002, '2021-09-01 12:01:01', null, null),
  32. (1002, 9002, '2021-02-02 19:01:01', '2021-02-02 19:30:01', 87),
  33. (1002, 9001, '2021-05-05 18:01:01', '2021-05-05 18:59:02', 90),
  34. (1003, 9001, '2021-02-06 12:01:01', null, null),
  35. (1003, 9001, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 50);

解题思路

题目意思：计算所有用户完成SQL类别高难度试卷得分的截断平均值（去掉一个最大值和一个最小值后的平均值）

题意分解：

  • 关联作答记录和试卷信息：join examination_info using(exam_id)；
  • 筛选SQL高难度试卷：where tag=”SQL” and difficulty=”hard”
  • 计算截断平均值：(和-最大值-最小值) / (总个数-2): (sum(score) - max(score) - min(score)) / (count(score) - 2)


细节问题：

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

SQL代码

select tag, difficulty,
    round((sum(score) - max(score) - min(score)) / (count(score) - 2), 1) as clip_avg_score
from exam_record
join examination_info using(exam_id)
where tag="SQL" and difficulty="hard"