leecode - 1280. Students and Examinations

 

문제
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

 

SELECT s.student_id, s.student_name, sub.subject_name, COUNT(e.student_id) AS attended_exams
FROM Students s
CROSS JOIN Subjects sub
LEFT JOIN Examinations e ON s.student_id = e.student_id AND sub.subject_name = e.subject_name
GROUP BY s.student_id, s.student_name, sub.subject_name
ORDER BY s.student_id, sub.subject_name;

내가 생각하지 못한 점은 cross join이였다.

cross join은 x와 y를 다 곱하는 join으로 여기서는 각 학생마다 과목의 개수를 알아야 하므로

student와 subject를 cross join 하여 학생마다 각 과목을 부여하고

부여한 뉴 테이블을 left join을 통해 다시 연결하여 각 학생마다의 과목 수를 count 할 수 있게 한다.

 

 

1280. Students and Examinations

Students and Examinations - LeetCode