(SQL) - LeetCode (easy) 1280. Students and Examinations

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

Students and Examinations - LeetCode

cross join, left join을 사용해본 문제였습니다.

📕 풀이방법

📔 풀이과정

1. 모든 subject_name와 student_id가 나와야 하므로 students와 subjects table을 cross join해줍니다.

2. group by이용, student_id, student_name, subject_name을 하나의 unique한 group으로 묶어줍니다.

3. order by를 id, name에 대해서 오름차순으로 정렬해줍니다.

4. 해당 결과를 토대로 select해줍니다.

---

📕 Code

📔 ANSI SQL

select s.student_id, student_name, su.subject_name, case when s.student_id is null then 0 else count(e.subject_name) end as attended_exams
from Students s
cross join Subjects su
left join Examinations e on s.student_id = e.student_id and su.subject_name = e.subject_name
group by s.student_id, s.student_name, su.subject_name
order by s.student_id, su.subject_name

---

*더 나은 내용을 위한 지적, 조언은 언제나 환영합니다.