題目
Table: Student
+---------------------+---------+
| Column Name | Type |
+---------------------+---------+
| student_id | int |
| student_name | varchar |
+---------------------+---------+
student_id is the primary key for this table.
student_name is the name of the student.
Table: Exam
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| exam_id | int |
| student_id | int |
| score | int |
+---------------+---------+
(exam_id, student_id) is the primary key for this table.
Student with student_id got score points in exam with id exam_id.
A "quite" student is the one who took at least one exam and didn't score neither the high score nor the low score.
Write an SQL query to report the students (student_id, student_name) being "quiet" in ALL exams.
Don't return the student who has never taken any exam. Return the result table ordered by student_id.
The query result format is in the following example.
Student table:
+-------------+---------------+
| student_id | student_name |
+-------------+---------------+
| 1 | Daniel |
| 2 | Jade |
| 3 | Stella |
| 4 | Jonathan |
| 5 | Will |
+-------------+---------------+
Exam table:
+------------+--------------+-----------+
| 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 |
+------------+--------------+-----------+
Result table:
+-------------+---------------+
| student_id | student_name |
+-------------+---------------+
| 2 | Jade |
+-------------+---------------+
For exam 1: Student 1 and 3 hold the lowest and high score respectively.
For exam 2: Student 1 hold both highest and lowest score.
For exam 3 and 4: Studnet 1 and 4 hold the lowest and high score respectively.
Student 2 and 5 have never got the highest or lowest in any of the exam.
Since student 5 is not taking any exam, he is excluded from the result.
So, we only return the information of Student 2.
生成數(shù)據(jù)
CREATE TABLE Student3(
student_id INT,
student_name VARCHAR(20));
CREATE TABLE Exam(
exam_id INT,
student_id INT,
score INT);
INSERT INTO Student3 VALUE(1, 'Daniel'),(2, 'Jade'),(3, 'Stella'),(4, 'Jonathan'),(5, 'Will');
INSERT INTO Exam VALUE(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);
解答
就是選出在每一門課中都沒有取到最高分也沒有取到最低分的同學(xué) 但是他得參加考試
對(duì)exam_id進(jìn)行分組 可以得到最高分和最低分
用二元in可以選出得到過最低分和最高分的同學(xué)
在exam中排除以上的同學(xué)即可
最后和Student 表左連接
先選出最高分和最低分
SELECT E.`exam_id`, MAX(E.`score`) AS max_sc
FROM Exam AS E
GROUP BY E.`exam_id`
UNION
SELECT E.`exam_id`, MIN(E.`score`) AS min_sc
FROM Exam AS E
GROUP BY E.`exam_id`;
再選出得到過最高分或最低分的同學(xué)
SELECT DISTINCT EEE.`student_id`
FROM Exam AS EEE
WHERE (EEE.`exam_id`, EEE.`score`) IN (SELECT E.`exam_id`, MAX(E.`score`) AS max_sc
FROM Exam AS E
GROUP BY E.`exam_id`
UNION
SELECT E.`exam_id`, MIN(E.`score`) AS min_sc
FROM Exam AS E
GROUP BY E.`exam_id`);
排除以上的同學(xué)
SELECT DISTINCT EE.`student_id`
FROM Exam AS EE
WHERE EE.`student_id` NOT IN (SELECT DISTINCT EEE.`student_id`
FROM Exam AS EEE
WHERE (EEE.`exam_id`, EEE.`score`) IN (SELECT E.`exam_id`, MAX(E.`score`) AS max_sc
FROM Exam AS E
GROUP BY E.`exam_id`
UNION
SELECT E.`exam_id`, MIN(E.`score`) AS min_sc
FROM Exam AS E
GROUP BY E.`exam_id`));
最后兩表連接即可 左連接
SELECT S.`student_id`, S.`student_name`
FROM (SELECT DISTINCT EE.`student_id`
FROM Exam AS EE
WHERE EE.`student_id` NOT IN (SELECT DISTINCT EEE.`student_id`
FROM Exam AS EEE
WHERE (EEE.`exam_id`, EEE.`score`) IN (SELECT E.`exam_id`, MAX(E.`score`) AS max_sc
FROM Exam AS E
GROUP BY E.`exam_id`
UNION
SELECT E.`exam_id`, MIN(E.`score`) AS min_sc
FROM Exam AS E
GROUP BY E.`exam_id`))) AS tmp
LEFT JOIN Student3 AS S
ON tmp.student_id = S.`student_id`;
