Leecode_SQL50_1280. Students and Examinations

1280. Students and Examinations

Problem description

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.

My solution

The first WITH:

SELECT * 
    FROM Students s
        CROSS JOIN Subjects su

用 CROSS JOIN 获取所有学生和科目的组合，不用有相同的列来 JOIN ON.
Output:

student_id	student_name	subject_name
1	Alice	Programming
1	Alice	Physics
1	Alice	Math
2	Bob	Programming
2	Bob	Physics
2	Bob	Math
13	John	Programming
13	John	Physics
13	John	Math
6	Alex	Programming
6	Alex	Physics
6	Alex	Math

The second WITH:

    SELECT student_id, subject_name, COUNT(*) AS attended_exams
    FROM Examinations
    GROUP BY student_id, subject_name

Output:

student_id	subject_name	attended_exams
1	Math	3
1	Physics	2
1	Programming	1
2	Programming	1
13	Math	1
13	Programming	1
13	Physics	1
2	Math	1

Combine them:

WITH a AS (
    SELECT * 
    FROM Students s
        CROSS JOIN Subjects su
), 
j AS (
    SELECT student_id, subject_name, COUNT(*) AS attended_exams
    FROM Examinations
    GROUP BY student_id, subject_name
)
SELECT a.student_id, a.student_name, a.subject_name, COALESCE(j.attended_exams, 0) AS attended_exams
FROM a
LEFT JOIN j
    ON a.student_id = j.student_id
        AND a.subject_name = j.subject_name
ORDER BY a.student_id ASC, a.subject_name ASC

注意一定要选择 a.subject_name！因为只有这个表是全的。若选择错了，有人的 subject_name 会是 null.