#86. [LeetCode / MySQL] 1280. Students and Examinations

문제

 

입력 코드

1. COUNT

SELECT 
    st.student_id, st.student_name, 
    sub.subject_name, 
    COUNT(ex.student_id) AS attended_exams
FROM Students AS st
CROSS JOIN Subjects AS sub
LEFT JOIN Examinations AS ex
    ON (st.student_id = ex.student_id) AND (sub.subject_name = ex.subject_name)
GROUP BY st.student_id, st.student_name, sub.subject_name
ORDER BY st.student_id, sub.subject_name;

 

2. COALESCE

SELECT 
    st.student_id, st.student_name, 
    sub.subject_name, 
    COALESCE(ex.attended_exams, 0) AS attended_exams
FROM Students AS st
CROSS JOIN Subjects AS sub
LEFT JOIN (
    SELECT student_id, subject_name, COUNT(*) AS attended_exams
    FROM Examinations
    GROUP BY student_id, subject_name
) AS ex
    ON (st.student_id = ex.student_id) AND (sub.subject_name = ex.subject_name)
ORDER BY st.student_id, sub.subject_name;

→ COALESCE를 이용해서 'attended_exams' 컬럼의 NULL 값을 0으로 바꿈

→ 학생이 특정 과목에 대한 시험에 참석하지 않은 경우를 처리함

 

 

COALESCE(expression1, expression1, expression1, ...) 함수

 

1. 컬럼의 NULL값 처리

• 컬럼의 NULL값을 기본값(default)으로 바꾸는 데 사용

SELECT COALESCE(column_name, 'default') AS replaced_value
FROM table_name;

 

 

2. NULL이 아닌 첫 번째 값 선택

• NULL이 아닌 첫 번째 값을 선택하는 데 사용

SELECT COALESCE(column1, column2, column3) AS first_non_null_value 
FROM table_name;

 

 

코드 설명

#SELECT #COUNT #JOIN #CROSS JOIN #LEFT JOIN #GROUP BY #ORDER BY #COALESCE

 

 

문제 출처

Students and Examinations - LeetCode