[Programmers] 물고기 종류 별 대어 찾기 (MYSQL)

프로그래머스

 

풀이

 

-- 코드를 작성해주세요
SELECT C.ID, F.FISH_NAME, F.LENGTH
FROM (FISH_INFO C JOIN FISH_NAME_INFO D ON C.FISH_TYPE = D.FISH_TYPE) JOIN (SELECT MAX(A.LENGTH) AS LENGTH, B.FISH_NAME
FROM FISH_INFO A JOIN FISH_NAME_INFO B ON A.FISH_TYPE = B.FISH_TYPE
GROUP BY B.FISH_NAME) F ON C.LENGTH = F.LENGTH AND D.FISH_NAME = F.FISH_NAME
ORDER BY C.ID;

 

굉장히 .. 지저분한 코드 여기서 일단 테이블을 분리해보면,

 

-- 코드를 작성해주세요

WITH TOP AS(
    SELECT MAX(A.LENGTH) AS LENGTH, B.FISH_NAME
    FROM FISH_INFO A JOIN FISH_NAME_INFO B ON A.FISH_TYPE = B.FISH_TYPE
    GROUP BY B.FISH_NAME
)

SELECT C.ID, TOP.FISH_NAME, TOP.LENGTH
FROM (FISH_INFO C JOIN FISH_NAME_INFO D ON C.FISH_TYPE = D.FISH_TYPE) JOIN TOP 
ON C.LENGTH = TOP.LENGTH AND D.FISH_NAME = TOP.FISH_NAME
ORDER BY C.ID;

 

이렇게 TOP 테이블을 하나 따로 분리해서 생성해줬다.

 

처음 생각은 FISH_TYPE으로 GROUP BY 하면 될 거라 생각했는데, ID와 FISH_TYPE이 둘 다 int 타입이라서 불가능했다.