[Cross Join] Students and Examinations

 

링크

https://leetcode.com/problems/students-and-examinations/description/

문제

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, su.subject_name,
        count(e.student_id) as attended_exams
from students s
cross join subjects su
left join examinations e
on su.subject_name = e.subject_name and S.student_id = E.student_id
group by s.student_id, s.student_name, su.subject_name
order by 1, 2, 3

해설

• cross join으로 모든 학생이 동일한 과목으로 정리될 수 있게 새로운 테이블 생성

새롭게 이해한 내용