8/28 每日一題 (MYSQL 三個表格合併 查詢學生各個科目的考試次數)

 

Table: Students

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| student_id    | int     |
| student_name  | varchar |
+---------------+---------+
student_id is the primary key (column with unique values) for this table.
Each row of this table contains the ID and the name of one student in the school.

 

Table: Subjects

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| subject_name | varchar |
+--------------+---------+
subject_name is the primary key (column with unique values) for this table.
Each row of this table contains the name of one subject in the school.

 

Table: Examinations

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| student_id   | int     |
| subject_name | varchar |
+--------------+---------+
There is no primary key (column with unique values) for this table. It may contain duplicates.
Each student from the Students table takes every course from the Subjects table.
Each row of this table indicates that a student with ID student_id attended the exam of subject_name.

 

Write a solution to find the number of times each student attended each exam.

Return the result table ordered by student_id and subject_name.

The result format is in the following example.

 

Example 1:

Input: 
Students table:
+------------+--------------+
| student_id | student_name |
+------------+--------------+
| 1          | Alice        |
| 2          | Bob          |
| 13         | John         |
| 6          | Alex         |
+------------+--------------+
Subjects table:
+--------------+
| subject_name |
+--------------+
| Math         |
| Physics      |
| Programming  |
+--------------+
Examinations table:
+------------+--------------+
| student_id | subject_name |
+------------+--------------+
| 1          | Math         |
| 1          | Physics      |
| 1          | Programming  |
| 2          | Programming  |
| 1          | Physics      |
| 1          | Math         |
| 13         | Math         |
| 13         | Programming  |
| 13         | Physics      |
| 2          | Math         |
| 1          | Math         |
+------------+--------------+
Output: 
+------------+--------------+--------------+----------------+
| student_id | student_name | subject_name | attended_exams |
+------------+--------------+--------------+----------------+
| 1          | Alice        | Math         | 3              |
| 1          | Alice        | Physics      | 2              |
| 1          | Alice        | Programming  | 1              |
| 2          | Bob          | Math         | 1              |
| 2          | Bob          | Physics      | 0              |
| 2          | Bob          | Programming  | 1              |
| 6          | Alex         | Math         | 0              |
| 6          | Alex         | Physics      | 0              |
| 6          | Alex         | Programming  | 0              |
| 13         | John         | Math         | 1              |
| 13         | John         | Physics      | 1              |
| 13         | John         | Programming  | 1              |
+------------+--------------+--------------+----------------+
Explanation: 
The result table should contain all students and all subjects.
Alice attended the Math exam 3 times, the Physics exam 2 times, and the Programming exam 1 time.
Bob attended the Math exam 1 time, the Programming exam 1 time, and did not attend the Physics exam.
Alex did not attend any exams.
John attended the Math exam 1 time, the Physics exam 1 time, and the Programming exam 1 time.

# Write your MySQL query statement below

SELECT

Students.student_id ,

Students.student_name ,

Subjects.subject_name,

coalesce(COUNT(Examinations.subject_name),0) AS attended_exams

FROM Students

CROSS JOIN  Subjects

LEFT JOIN Examinations on Students.student_id =Examinations.student_id AND Subjects.subject_name = Examinations.subject_name

GROUP BY Students.student_id, Students.student_name, Subjects.subject_name

ORDER BY Students.student_id, Subjects.subject_name

# 選擇學生ID 姓名,科目,然後使用COALESCE函數將學生參加每科的考試次數,如果不存在就返回0

# 因為有3個表 使用CROSS JOIN 將學生表和科目表組合

# 然後使用 LEFT JOIN 將上述組合與考試表進行連接, 基於

Students.student_id =Examinations.student_id

AND Subjects.subject_name = Examinations.subject_name

#然後使用分組針對學生ID 姓名和科目,確保所有學生在每個科目的考試次數被正確計算

-------------參考解答

# Write your MySQL query statement below
select a.student_id,a.student_name,c.subject_name,count(b.student_id) as attended_exams
from Students a 

cross join Subjects c 
left join Examinations b
on a.student_id=b.student_id and c.subject_name =b.subject_name
group by a.student_id,a.student_name,c.subject_name
order by a.student_id,a.student_name,c.subject_name