https://leetcode.com/problems/students-and-examinations/?envType=study-plan-v2&envId=top-sql-50
코드설명
LEFT OUTER JOIN + INNER JOIN + GROUP BY + SCALAR SUBQUERY 를 활용합니다.
정답코드1번의 경우, 정석적인 풀이입니다.
문제에서 유의해야하는 것은, 시험을 0번본 경우도 결과레코드에 포함시켜야 한다는 점입니다.
모든 학생이, 모든 SUBJECT에 시험을 봐야하고, 각 시험마다 몇번을 봤는지를 나타내야 하기에 그렇습니다.
처음에 해당사항을 놓치고, STUDENT, SUBJECT, EXAMINATIONS 를 한번에 INNER JOIN 시켜서 GROUP BY 하니, 학생이 어떤 과목에 대해 0번 시험 본 정보는 나오지 않았습니다. (STUDENT와 EXAMINATIONS에서 INNER JOIN 하면서 시험보지 않은 과목은 걸러지기 때문입니다.) 아래가 틀린 코드입니다.
SELECT E.STUDENT_ID AS studentid, S.STUDENT_NAME AS student_name, E.SUBJECT_NAME AS subject_name, COUNT(*) AS attended_exams
FROM STUDENTS S, SUBJECTS SUB, EXAMINATIONS E
WHERE S.STUDENT_ID = E.STUDENT_ID
AND E.SUBJECT_NAME = SUB.SUBJECT_NAME
GROUP BY E.STUDENT_ID, S.STUDENT_NAME, E.SUBJECT_NAME
ORDER BY E.STUDENT_ID, E.SUBJECT_NAME;
그렇다면, 어떻게 해야할까요?
STDUENT와 SUBJECT를 먼저 CROSS JOIN (카타시안 조인) 한뒤,
0인것도 개수를 세기 위해 LEFT OUTER JOIN을 통해 처리합니다.
해당 코드는 하단 정답코드 1번에 존재합니다.
그리고 위의 코드의 경우 인덱스를 어떻게 만들든 간에 부분범위처리가 불가합니다.
LEFT OUTER JOIN의 경우 SELECT-LIST에 SCALAR SUBQUERY로 변환이 가능합니다.
코드를 변환한 코드는 하단 정답코드2입니다.
MYSQL 코드
정답코드 1입니다.
SELECT S.STUDENT_ID AS student_id, S.STUDENT_NAME AS student_name, SUB.SUBJECT_NAME AS subject_name, COUNT(E.STUDENT_ID) AS attended_exams
FROM STUDENTS S
INNER JOIN SUBJECTS SUB
LEFT OUTER JOIN EXAMINATIONS E
ON E.STUDENT_ID = S.STUDENT_ID
AND E.SUBJECT_NAME = SUB.SUBJECT_NAME
GROUP BY S.STUDENT_ID, S.STUDENT_NAME, SUB.SUBJECT_NAME
ORDER BY S.STUDENT_ID, SUB.SUBJECT_NAME정답코드2입니다.
SELECT S.STUDENT_ID AS student_id, S.STUDENT_NAME AS student_name, SUB.SUBJECT_NAME AS subject_name,
(SELECT COUNT(*)
FROM EXAMINATIONS E
WHERE E.STUDENT_ID = S.STUDENT_ID
AND E.SUBJECT_NAME = SUB.SUBJECT_NAME
) AS attended_exams
FROM STUDENTS S
CROSS JOIN SUBJECTS SUB
ORDER BY S.STUDENT_ID, SUB.SUBJECT_NAME