[SQL] 즐겨찾기가 가장 많은 식당 정보 출력하기, 식품 분류 별 가장 비싼 식품의 정보 조회하기, 5월 식품들의 총 매출 조회하기, 없어진 기록 찾기, 과일로 만든 아이스크림 고르기, 재구매가 일어난 상품과 회원 리스트 구하기

즐겨찾기가 가장 많은 식당 정보 출력하기

SELECT food_type,
    rest_id,
    rest_name,
    favorites
FROM (SELECT food_type, rest_id, rest_name, favorites,
     ROW_NUMBER() OVER (PARTITION BY food_type ORDER BY favorites DESC) AS rown
     FROM rest_info
     ) AS a
WHERE rown=1
ORDER BY food_type DESC

 

식품 분류 별 가장 비싼 식품의 정보 조회하기

SELECT category, price AS max_price, product_name
FROM (SELECT category, price, product_name,
     ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rown
     FROM food_product
     ) AS a
WHERE category IN ('과자', '국', '김치', '식용유')
AND rown=1
ORDER BY price DESC

 

5월 식품들의 총매출 조회하기

SELECT p.product_id,
    p.product_name,
    SUM(p.price * o.amount) AS total_sales
FROM food_product p INNER JOIN food_order o ON p.product_id=o.product_id
WHERE DATE_FORMAT(o.produce_date, '%Y-%m') LIKE '2022-05%'
GROUP BY 1, 2
ORDER BY 3 DESC, 1

 

없어진 기록 찾기

SELECT o.animal_id, o.name
FROM animal_outs o LEFT JOIN animal_ins i ON o.animal_id=i.animal_id
WHERE i.animal_id IS NULL
ORDER BY o.animal_id

 

과일로 만든 아이스크림 고르기

SELECT f.flavor
FROM first_half f INNER JOIN icecream_info i ON f.flavor=i.flavor
WHERE f.total_order>3000
AND i.ingredient_type='fruit_based'
ORDER BY total_order DESC

 

재구매가 일어난 상품과 회원 리스트 구하기

SELECT user_id, product_id
FROM online_sale
GROUP BY user_id, product_id
HAVING COUNT(product_id)>1
ORDER BY user_id, product_id DESC