[JOIN/LEVEL4] 그룹별 조건에 맞는 식당 목록 출력하기 (MySQL/Oracle)

MySQL

SELECT MEMBER_NAME
, REVIEW_TEXT
, DATE_FORMAT(REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM MEMBER_PROFILE T1, REST_REVIEW T2
WHERE T1.MEMBER_ID  = T2.MEMBER_ID
AND T1.MEMBER_ID = ( SELECT MEMBER_ID
                      FROM (SELECT    A.MEMBER_ID
                                    , COUNT(*)
                            FROM MEMBER_PROFILE A, REST_REVIEW B
                            WHERE A.MEMBER_ID = B.MEMBER_ID
                            GROUP BY A.MEMBER_ID
                            ORDER BY COUNT(*) DESC, REVIEW_DATE, REVIEW_TEXT
                            LIMIT 1) C )
ORDER BY REVIEW_DATE, REVIEW_TEXT

 

 

---

 

 

Oracle

SELECT    A.PRODUCT_ID
        , A.PRODUCT_NAME
        , SUM(A.PRICE * B.AMOUNT) AS TOTAL_SALES
FROM FOOD_PRODUCT A, FOOD_ORDER B
WHERE A.PRODUCT_ID = B.PRODUCT_ID
AND TO_CHAR(B.PRODUCE_DATE, 'YYYYMM') = '202205'
GROUP BY A.PRODUCT_ID, A.PRODUCT_NAME
ORDER BY TOTAL_SALES DESC, A.PRODUCT_ID ASC
;