1412. 查找成绩处于中游的学生

Leetcode题目:查询介于最大和最小之间的值

表: Student

+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| student_id          | int     |
| student_name        | varchar |
+---------------------+---------+
student_id 是该表主键(具有唯一值的列)。
student_name 学生名字。

表: Exam

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| exam_id       | int     |
| student_id    | int     |
| score         | int     |
+---------------+---------+
(exam_id, student_id) 是该表主键(具有唯一值的列的组合)。
学生 student_id 在测验 exam_id 中得分为 score。

成绩处于中游的学生是指至少参加了一次测验, 且得分既不是最高分也不是最低分的学生。

编写解决方案,找出在 所有 测验中都处于中游的学生 (student_id, student_name)。不要返回从来没有参加过测验的学生。

返回结果表按照 student_id 排序。

返回结果格式如下。

示例 1:输入: Student 表: +————-+—————+ | student_id | student_name | +————-+—————+ | 1 | Daniel | | 2 | Jade | | 3 | Stella | | 4 | Jonathan | | 5 | Will | +————-+—————+ Exam 表: +————+————–+———–+ | exam_id | student_id | score | +————+————–+———–+ | 10 | 1 | 70 | | 10 | 2 | 80 | | 10 | 3 | 90 | | 20 | 1 | 80 | | 30 | 1 | 70 | | 30 | 3 | 80 | | 30 | 4 | 90 | | 40 | 1 | 60 | | 40 | 2 | 70 | | 40 | 4 | 80 | +————+————–+———–+ 输出: +————-+—————+ | student_id | student_name | +————-+—————+ | 2 | Jade | +————-+—————+ 解释: 对于测验 1: 学生 1 和 3 分别获得了最低分和最高分。 对于测验 2: 学生 1 既获得了最高分, 也获得了最低分。 对于测验 3 和 4: 学生 1 和 4 分别获得了最低分和最高分。 学生 2 和 5 没有在任一场测验中获得了最高分或者最低分。 因为学生 5 从来没有参加过任何测验, 所以他被排除于结果表。 由此, 我们仅仅返回学生 2 的信息。

解题思路

NOT IN+ALL

查询某个在全部group中,都小于最大值,但大于最小值的某个中间值。

那么首先得选出不同的分组,再用每个student的score去比较每个组的最大值和最小值。如果在任意分组中拿过最大值或最小值,那么就把对应的student_id排除出结果集。

# Write your MySQL query statement below
SELECT DISTINCT A.student_id,B.student_name
FROM EXAM AS A 
LEFT JOIN Student AS B 
ON A.student_id=B.student_id
WHERE A.student_id NOT IN(
  SELECT C.student_id
  FROM EXAM AS C
  WHERE C.score>=(
    SELECT MAX(score) FROM EXAM
    WHERE C.exam_id=exam_id
    GROUP BY exam_id)
  OR C.score<=(
    SELECT MIN(score) FROM EXAM
    WHERE exam_id=C.exam_id
    GROUP BY exam_id
  )
)
ORDER BY A.student_id ASC

Subscribe
提醒
0 评论
Inline Feedbacks
View all comments
0
在此留下你的评论x