[SQL 문제 풀이] 5월 식품들의 총매출 조회하기

내가 작성한 쿼리

Oracle

SELECT
  FO.PRODUCT_ID,
  FP.PRODUCT_NAME,
  SUM(FP.PRICE * FO.AMOUNT) AS TOTAL_PRICE
FROM
  FOOD_ORDER FO
JOIN
  FOOD_PRODUCT FP
ON
  FO.PRODUCT_ID = FP.PRODUCT_ID
WHERE
  FO.PRODUCE_DATE BETWEEN TO_DATE('2022-05-01', 'YYYY-MM-DD') AND TO_DATE('2022-05-31', 'YYYY-MM-DD')
GROUP BY
  FO.PRODUCT_ID, FP.PRODUCT_NAME
ORDER BY
  TOTAL_PRICE DESC, FO.PRODUCT_ID
;

MySQL

SELECT
  FO.PRODUCT_ID,
  FP.PRODUCT_NAME,
  SUM(FP.PRICE * FO.AMOUNT) AS TOTAL_PRICE
FROM
  FOOD_ORDER FO
JOIN
  FOOD_PRODUCT FP
ON
  FO.PRODUCT_ID = FP.PRODUCT_ID
WHERE
  FO.PRODUCE_DATE BETWEEN '2022-05-01' AND '2022-05-31'
GROUP BY
  FO.PRODUCT_ID, FP.PRODUCT_NAME
ORDER BY
  TOTAL_PRICE DESC, FO.PRODUCT_ID
;

차이점

• 날짜 비교 방식
• Oracle: TO_DATE를 이용해 문자열 형식을 날짜 형식으로 전환 및 날짜만 추출한 다음 비교
• MySQL: 자동 형 변환을 이용하여 비교