[Leetcode] CROSS JOIN

1280. Students and Examinations

 

각 학생들의 시험과목별 응시횟수 조회하기

 

[내 답안]

WITH exams AS ( -- 학생별 시험별 응시횟수
SELECT e.student_id,
       s.student_name,
       e.subject_name,
       COUNT(*) AS attended_exams
FROM examinations e
JOIN students s ON e.student_id = s.student_id
GROUP BY e.student_id, s.student_name, e.subject_name
ORDER BY e.student_id
)

SELECT e1.student_id,
       e3.student_name,
       e1.subject_name,
       IF(e2.attended_exams IS NULL, 0, e2.attended_exams) AS attended_exams
FROM (
    SELECT s1.student_id, s2.subject_name
    FROM students s1
    CROSS JOIN subjects s2 -- 연결되는 column 없음
) e1
LEFT JOIN exams e2 ON e1.student_id = e2.student_id AND
                     e1.subject_name = e2.subject_name
JOIN students e3 ON e1.student_id = e3.student_id
ORDER BY student_id, subject_name

 

1. 과목별 응시 횟수 정보가 있는 examinations 테이블을 먼저 group by 한 결과를 WITH exams 테이블로 생성한다.

2. 학생 정보 students 테이블과 과목 정보 subjects 테이블을 CROSS JOIN한다.

3. 두개를 JOIN으로 붙여준다.

하나씩 해보고 붙여넣은 식으로 한거라서 뭔가 쓸데없이 코드가 복잡하고 길어진 느낌이 있다.

 

 

 

테이블 형태를 먼저 만든 뒤 group by를 하면 코드를 더 단순화 할 수 있을 것 같아서 다시 코드를 짜 보았다.

[수정한 코드]

SELECT s1.student_id, s1.student_name, s2.subject_name,
       COUNT(e.student_id) AS attended_exams -- 4.examinations 기준으로 count
FROM students s1
CROSS JOIN subjects s2 -- 1.student와 subject의 행을 cross하여 테이블 join
LEFT JOIN examinations e ON s1.student_id = e.student_id AND -- 2.examinations를 앞의 행에 left join
                            s2.subject_name = e.subject_name
GROUP BY s1.student_id, s1.student_name, s2.subject_name -- 3.1에서 만든 테이블로 group by
ORDER BY s1.student_id, s2.subject_name -- 5.학생 이름, 과목명 순으로 오름차순 정렬

WITH도 활용할 필요 없이 훨씬 간단하게 쿼리를 수정할 수 있었다.