[LeetCode] Students and Examinations_CROSS JOIN

문제

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.

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              |
+------------+--------------+--------------+----------------+

문제 요약
학생이 시험을 봤든 안봤든 '학생의 이름'과 '과목 이름', '참여한 시험 횟수'를 모두 추출하라.
시험을 한 번도 보지 않았으면 '참여한 시험 횟수'를 0으로 두고 추출하라

 

풀이

처음 시도한 풀이(실패)

SELECT s.student_id, s.student_name, b.subject_name, COUNT(b.subject_name)
FROM students s
LEFT JOIN examinations e
ON s.student_id = e.student_id
RIGHT JOIN subjects b
ON b.subject_name = e.subject_name
GROUP BY 1, 2, 3
ORDER BY 1, 3

오른쪽과 같이 시험을 한 번도 치르지 않은 학생들도 추출되어야 하는데, 나오지 않았다. 

 

참고한 팀원 풀이

SELECT s.student_id AS student_id
    , s.student_name AS student_name
    , sb.subject_name AS subject_name
    ,count(e.subject_name) AS attended_exams
FROM students s
CROSS JOIN subjects sb
LEFT JOIN examinations e 
ON s.student_id = e.student_id AND sb.subject_name = e.subject_name
GROUP BY 1, 3
ORDER BY 1, 3;

 

 1. students 테이블과 subjects 테이블을 cross join한 후 살펴 보자.

SELECT *
FROM students s
CROSS JOIN subjects sb

: 모든 학생들이 과목 당 한번 씩 다 출력된 것을 확인할 수 있다. 

 

2. 그렇게 corss join된 테이블을 기준으로 examinations 테이블과 join 해준다.

3. count값을 추출해야 하므로 학생 id와 과목 이름으로 그룹핑 해준다. 

: 제대로 출력된 것을 확인 할 수 있다.