링크: https://leetcode.com/problems/students-and-examinations/
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.문제풀이
목표: 각 학생이 시험에 참석한 횟수를 구하기. 결과는 student_id, subject_name 으로 정렬
내 풀이:
각 학생이 시험에 참석한 횟수를 구하는데 Subjects 테이블이 왜 있는걸까? 이렇게 생각하고 출력 예시를 보니 모든 학생들의 모든 시험에 대한 응시 횟수를 구해야 했다.
그래서 나중에 Subjects 테이블의 subjcet_name 을 GROUP BY 에 추가해서 Examinations 테이블에 해당하는 subjcet_name 이 없다면 0 을 출력해줘야 하는데, 문제는 이 세 개의 테이블을 어떤 방식으로 JOIN 해야 하는가였다.
그렇게 LEFT, RIGHT 여러 방면으로 고민해보다 CROSS JOIN 이라는 것을 알게됐다.
CROSS JOIN이란 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시키는 기능이다.
출처: https://hongong.hanbit.co.kr/sql-기본-문법-joininner-outer-cross-self-join/
이 CROSS JOIN 을 활용해 먼저 Sutdent 테이블과 Subjects 테이블을 JOIN 시켜주고, 그 다음에 LEFT JOIN 을 활용해 Examinations 테이블을 JOIN 하면 우리가 원하는 결과를 만들 수 있다.
최종코드
SELECT
st.student_id,
st.student_name,
su.subject_name,
COUNT(ex.subject_name) attended_exams
FROM Students st
CROSS JOIN Subjects su
LEFT JOIN Examinations ex
ON st.student_id = ex.student_id AND su.subject_name = ex.subject_name
GROUP BY 1, 2, 3
ORDER BY 1, 2;