[MY SQL] 2개의 테이블로 종류별 가장 큰 값이 있는 레코드 추출

출처: [프로그래머스 스쿨 - 코딩테스트 연습 - MY SQL 문제] :  물고기 종류 별 대어 찾기

링크: https://school.programmers.co.kr/learn/courses/30/lessons/293261

문제(일부):  물고기 종류 별로 가장 큰 물고기의 ID, 이름, 길이를 출력

*이름은 FISH_NAME_INFO / 이외 정보는 FISH_INFO

 

1. 서브쿼리 활용법(1)

2개의 테이블을 join해서 출력할 정보를 선택
'가장 큰 물고기'는 where절에 서브쿼리에서 필터링
* WHERE는 행을 기준으로 비교하므로 서브쿼리에서 종류가 일치하는 것 중 가장 큰 값을 선택하므로 종류별로 가장 큰 값을 필터링

SELECT fi.ID, fn.FISH_NAME, fi.LENGTH
FROM FISH_INFO AS fi
    INNER JOIN FISH_NAME_INFO AS fn
            ON fi.FISH_TYPE = fn.FISH_TYPE
            
# 서브쿼리에서 종류가 일치하는 것 중 가장 큰 값을 선택
WHERE fi.LENGTH = (
    SELECT MAX(LENGTH) 
    FROM FISH_INFO 
    WHERE FISH_TYPE = fi.FISH_TYPE)

 

2. 서브쿼리 활용법(2)

1번 방법과 SELECT FROM은 동일, 서브쿼리에서 group by를 활용하여 종류별로 가장 큰 물고기를 필터링
* 주의할 점(1)
   WHERE fi.LENGTH = 서브쿼리 / 그룹별로 최대값이 나오므로 결과가 2개 이상 => "="사용불가
*주의할점(2)
서브쿼리의 값이 (fi.FISH_TYPE, fi.LENGTH) 타입과 길이 모두 일치하는지 확인,

fi.LENGTH IN (서브쿼리) => 종류와 상관없이 종류별 길이의 최대값과 일치하면 조건을 만족
ex) 작은생선 종류의 max값이 8, 중형 생선 max값이 10일 때 중형생선에 8인 레코드가 있다면 조건을 만족

SELECT fi.ID, fn.FISH_NAME, fi.LENGTH
FROM FISH_INFO AS fi
    INNER JOIN FISH_NAME_INFO AS fn
            ON fi.FISH_TYPE = fn.FISH_TYPE
WHERE (fi.FISH_TYPE, fi.LENGTH) IN (
    SELECT FISH_TYPE, MAX(LENGTH)
    FROM FISH_INFO
    GROUP BY FISH_TYPE)

 

3. WITH절 활용

1) WITH절에서 MAX_FISH에 대한 정보를 추출
2) 메인쿼리에서 물고기 이름이 있는 테이블과 JOIN

WITH MAX_FISH AS (SELECT FISH_TYPE, ID, LENGTH  
    FROM FISH_INFO
    WHERE (FISH_TYPE, LENGTH) IN (
        SELECT FISH_TYPE, 
        MAX(LENGTH) AS LENGTH
        FROM FISH_INFO
        GROUP BY FISH_TYPE ))

SELECT m.ID,
        n.FISH_NAME,
        m.LENGTH
FROM MAX_FISH AS m
    INNER JOIN FISH_NAME_INFO AS n
        ON m.FISH_TYPE = n.FISH_TYPE
ORDER BY m.ID