[MySQL] 프로그래머스 물고기 종류 별 대어 찾기

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

프로그래머스

코딩테스트 연습 > SUM, MAX, MIN > 물고기 종류 별 대어 찾기

 

문제 분석

난이도: LEVEL3

 

문제 요구사항

물고기 종류 별로 가장 큰 물고기의 ID, 물고기 이름, 길이를 출력하는 SQL 문을 작성해주세요.
물고기의 ID 컬럼명은 ID, 이름 컬럼명은 FISH_NAME, 길이 컬럼명은 LENGTH로 해주세요.
결과는 물고기의 ID에 대해 오름차순 정렬해주세요.
단, 물고기 종류별 가장 큰 물고기는 1마리만 있으며 10cm 이하의 물고기가 가장 큰 경우는 없습니다.

 

정답 코드1

SELECT I1.ID, N.FISH_NAME, I1.LENGTH
FROM 
FISH_INFO I1 JOIN (SELECT MAX(LENGTH) MAX_LENGTH, FISH_TYPE
                   FROM FISH_INFO
                   GROUP BY FISH_TYPE 
                  ) I2
ON I1.LENGTH = I2.MAX_LENGTH AND I1.FISH_TYPE = I2.FISH_TYPE
JOIN FISH_NAME_INFO N
ON I1.FISH_TYPE = N.FISH_TYPE
ORDER BY ID

 

FROM 절에서 서브 쿼리를 활용한 3개 조인 쿼리 

 

정답 코드2

WITH FISH_MAX AS (
    SELECT MAX(LENGTH) MAX_LENGTH, FISH_TYPE
    FROM FISH_INFO 
    GROUP BY FISH_TYPE
)
SELECT I.ID, N.FISH_NAME, M.MAX_LENGTH LENGTH
FROM 
FISH_MAX M 
JOIN 
FISH_INFO I
ON M.FISH_TYPE = I.FISH_TYPE AND I.LENGTH = M.MAX_LENGTH 
JOIN 
FISH_NAME_INFO N 
ON N.FISH_TYPE = M.FISH_TYPE
ORDER BY I.ID

 

CTE를 활용한 3개 조인 쿼리 

 

정답 코드3

SELECT I.ID,
       N.FISH_NAME,
       I.LENGTH 
FROM (SELECT *, RANK() OVER (PARTITION BY FISH_TYPE ORDER BY LENGTH DESC) R
      FROM FISH_INFO ) I 
JOIN FISH_NAME_INFO N
ON I.FISH_TYPE = N.FISH_TYPE
WHERE I.R = 1
ORDER BY I.ID

 

RANK() 윈도우 함수 활용