[02.06 코테(MySQL/Oracle)]-5월 식품들의 총매출 조회하기

Lv4. 5월 식품들의 총매출 조회하기 (MySQL)

링크: https://school.programmers.co.kr/learn/courses/30/lessons/131117?language=mysql

 

내가 작성한 코드

-- 풀이1 (날짜 범위)
SELECT P.PRODUCT_ID, P.PRODUCT_NAME, SUM(P.PRICE*O.AMOUNT) AS TOTAL_SALES 
FROM FOOD_PRODUCT P
JOIN FOOD_ORDER O
  ON P.PRODUCT_ID = O.PRODUCT_ID
WHERE O.PRODUCE_DATE >= '2022-05-01' AND O.PRODUCE_DATE < '2022-06-01'
GROUP BY P.PRODUCT_ID, P.PRODUCT_NAME
ORDER BY TOTAL_SALES DESC, P.PRODUCT_ID;

-- 풀이2 (DATE_FORMAT())
SELECT P.PRODUCT_ID, P.PRODUCT_NAME, SUM(P.PRICE*O.AMOUNT) AS TOTAL_SALES 
FROM FOOD_PRODUCT P
JOIN FOOD_ORDER O
  ON P.PRODUCT_ID = O.PRODUCT_ID
WHERE DATE_FORMAT(O.PRODUCE_DATE, '%Y-%m') = '2022-05'
GROUP BY P.PRODUCT_ID, P.PRODUCT_NAME
ORDER BY TOTAL_SALES DESC, P.PRODUCT_ID;

-- 풀이3 (WITH)
WITH MAY_ORDER AS (SELECT PRODUCT_ID, AMOUNT
                   FROM FOOD_ORDER
                   WHERE PRODUCE_DATE >= '2022-05-01' AND PRODUCE_DATE < '2022-06-01')
SELECT P.PRODUCT_ID, P.PRODUCT_NAME, SUM(P.PRICE*M.AMOUNT) AS TOTAL_SALES
FROM FOOD_PRODUCT P
JOIN MAY_ORDER M
  ON P.PRODUCT_ID = M.PRODUCT_ID
GROUP BY P.PRODUCT_ID, P.PRODUCT_NAME
ORDER BY TOTAL_SALES DESC, P.PRODUCT_ID;

 

Oracle

-- 풀이1 (날짜 범위)
SELECT P.PRODUCT_ID, P.PRODUCT_NAME, SUM(P.PRICE*O.AMOUNT) AS TOTAL_SALES 
FROM FOOD_PRODUCT P
JOIN FOOD_ORDER O
  ON P.PRODUCT_ID = O.PRODUCT_ID
WHERE O.PRODUCE_DATE >= DATE '2022-05-01' AND O.PRODUCE_DATE < DATE '2022-06-01'
GROUP BY P.PRODUCT_ID, P.PRODUCT_NAME
ORDER BY TOTAL_SALES DESC, P.PRODUCT_ID;

-- 풀이2 (TO_CHAR)
SELECT P.PRODUCT_ID, P.PRODUCT_NAME, SUM(P.PRICE*O.AMOUNT) AS TOTAL_SALES 
FROM FOOD_PRODUCT P
JOIN FOOD_ORDER O
  ON P.PRODUCT_ID = O.PRODUCT_ID
WHERE TO_CHAR(O.PRODUCE_DATE, 'YYYY-MM') = '2022-05'
GROUP BY P.PRODUCT_ID, P.PRODUCT_NAME
ORDER BY TOTAL_SALES DESC, P.PRODUCT_ID;

-- 풀이3 (인라인 뷰(서브쿼리FROM))
SELECT P.PRODUCT_ID, P.PRODUCT_NAME, P.PRICE * S.TOTAL_AMT AS TOTAL_SALES
FROM FOOD_PRODUCT P
JOIN (SELECT PRODUCT_ID, SUM(AMOUNT) AS TOTAL_AMT
      FROM FOOD_ORDER
      WHERE PRODUCE_DATE >= DATE '2022-05-01' AND PRODUCE_DATE < DATE '2022-06-01'
      GROUP BY PRODUCT_ID) S
  ON S.PRODUCT_ID = P.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, P.PRODUCT_ID