[Oracle] 그룹별 조건에 맞는 식당 목록 출력하기

https://school.programmers.co.kr/learn/courses/30/lessons/131124

정답

SELECT A.MEMBER_NAME,B.REVIEW_TEXT,TO_CHAR(B.REVIEW_DATE,'YYYY-MM-DD') REVIEW_DATE
FROM MEMBER_PROFILE A
    INNER JOIN REST_REVIEW B
    ON A.MEMBER_ID = B.MEMBER_ID
WHERE A.MEMBER_ID IN (
    SELECT MEMBER_ID
    FROM REST_REVIEW
    GROUP BY MEMBER_ID
    HAVING COUNT(*) =(
        SELECT MAX(COUNT(*))
        FROM REST_REVIEW
        GROUP BY MEMBER_ID
    )
)
ORDER BY REVIEW_DATE,B.REVIEW_TEXT

풀이

가장 많은 MEMBER_ID 횟수 구하기

SELECT MEMBER_ID,COUNT(*)
FROM REST_REVIEW
GROUP BY MEMBER_ID

SELECT MAX(COUNT(*))
FROM REST_REVIEW
GROUP BY MEMBER_ID

SELECT MEMBER_ID
    FROM REST_REVIEW
    GROUP BY MEMBER_ID
    HAVING COUNT(*) =(
        SELECT MAX(COUNT(*))
        FROM REST_REVIEW
        GROUP BY MEMBER_ID
    )

리뷰가 가장 많은 MEMBER_ID

SELECT A.MEMBER_NAME,B.REVIEW_TEXT,TO_CHAR(B.REVIEW_DATE,'YYYY-MM-DD') REVIEW_DATE
FROM MEMBER_PROFILE A
    INNER JOIN REST_REVIEW B
    ON A.MEMBER_ID = B.MEMBER_ID
WHERE A.MEMBER_ID IN (
    SELECT MEMBER_ID
    FROM REST_REVIEW
    GROUP BY MEMBER_ID
    HAVING COUNT(*) =(
        SELECT MAX(COUNT(*))
        FROM REST_REVIEW
        GROUP BY MEMBER_ID
    )
)
ORDER BY REVIEW_DATE,B.REVIEW_TEXT