[프로그래머스][SQL] 특정 조건을 만족하는 물고기별 수와 최대 길이 구하기, 물고기 종류 별 대어 찾기

FISH_INFO 관련 SQL 문제 - 프로그래머스 lv3

 

 

 

 

 

 

특정 조건을 만족하는 물고기별 수와 최대 길이 구하기

 

 

제출한 답안

select count(id) as FISH_COUNT, max(length) as MAX_LENGTH, fish_type
from fish_info
group by fish_type
having avg(ifnull(length,10)) >= 33
order by fish_type

 

 

 

물고기 종류 별 대어 찾기

 

 

제출한 답안

select A.id, B.fish_name, A.length as length
from fish_info A inner join fish_name_info B
on (A.fish_type = B.fish_type)
where (A.fish_type, A.length) in (select fish_type, max(length)
                                  from fish_info
                                  group by fish_type)
order by id

 

- 서브쿼리 활용