[SQL문제다시풀기] 식품분류별 가장 비싼 식품의 정보 조회하기

문제

MYSQL

내가 작성한 정답1 - 서브쿼리 이용

select category, price max_price, product_name
from food_product
where (category, price) in (select category, max(price) max_price
                            from food_product 
                            where category in ('과자', '국', '김치', '식용유')
                            group by category)
order by 2 desc

내가 작성한 정답2 - cte이용

with max_tb as (select category, max(price) price
                from food_product 
                where category in ('과자', '국', '김치', '식용유')
                group by category)
select category, price max_price, product_name
from food_product
where (category, price) in (select * from max_tb)
order by 2 desc

내가 작성한 정답3 - cte, join이용

with max_tb as (select category, max(price) price
                from food_product 
                where category in ('과자', '국', '김치', '식용유')
                group by category)
select m.category, m.price max_price, f.product_name
from food_product f
join max_tb m on f.category = m.category and f.price = m.price
order by 2 desc

ORACLE - order by 제외한 모든 코드가 같음

내가 작성한 정답1 - 서브쿼리 이용

select category, price max_price, product_name
from food_product
where (category,price) in (select category, max(price) price
                            from food_product
                            where category in ('과자', '국', '김치', '식용유')
                            group by category)
order by max_price desc

내가 작성한 정답2 - cte이용

with max_tb as (select category, max(price) price
                from food_product
                where category in ('과자', '국', '김치', '식용유')
                group by category)
select category, price max_price, product_name
from food_product
where (category, price) in (select * from max_tb)
order by max_price desc

내가 작성한 정답3 - cte, join이용

with max_tb as (select category, max(price) price
                from food_product
                where category in ('과자', '국', '김치', '식용유')
                group by category)
select m.category, m.price max_price, f.product_name
from food_product f 
join max_tb m on f.price = m.price and f.category = m.category
order by max_price desc