[LeetCode] 1280. Students and Examinations (JOIN)

< 문제 >

[SQL50] 1280. Students and Examinations (JOIN)

---

---

< 풀이 >

WITH attend_class AS(
    SELECT student_id, subject_name, SUM(1) AS attended_exams
    FROM Examinations
    GROUP BY student_id, subject_name
)

SELECT A.student_id, A.student_name, A.subject_name, CASE WHEN C.attended_exams IS NULL THEN 0 ELSE C.attended_exams END AS attended_exams
FROM (Students CROSS JOIN Subjects) AS A LEFT JOIN attend_class C ON A.student_id = C.student_id AND A.subject_name = C.subject_name
ORDER BY A.student_id, A.subject_name;

---

WITH ~ AS (...)

임시 테이블 생성해서 이후 JOIN 문에서 활용

https://youngone-kang.tistory.com/45

 

CASE WHEN

특정 열에서 가지는 값에 따라 다르게 출력

https://youngone-kang.tistory.com/48

 

JOIN

>> Students CROSS JOIN Subjects   

:  attend_class에 있는 (student_id, subject_name)과 매치하기 위해 students와 subjects의 모든 조합을 구한다.

 

>> ~ LEFT JOIN attend_class

:  앞서 cross join한 테이블에 학생별, 과목별 조합을 구한다.