프로그래머스 ORACLE 그룹별 조건에 맞는 식당 목록 출력하기

<오류1>

SELECT T1.MEMBER_ID, MAX(T1.REVIEW_SCORE) AS MAX_REVIEW_SCORE 
    FROM  REST_REVIEW T1
GROUP BY MEMBER_ID

 

 

<오류2>

--오류
  SELECT A.MEMBER_NAME, MAX(B.REVIEW_SCORE) 
    FROM MEMBER_PROFILE A
    INNER JOIN REST_REVIEW B ON A.MEMBER_ID = B.MEMBER_ID
    GROUP BY MEMBER_NAME
ORDER BY REVIEW_SCORE DESC

--수정
SELECT A.MEMBER_NAME, MAX(B.REVIEW_SCORE) AS REVIEW_SCORE --별칭적어야함 안적으면 컬럼명 MAX(B.REVIEW_SCORE)이거로됨
FROM MEMBER_PROFILE A
INNER JOIN REST_REVIEW B ON A.MEMBER_ID = B.MEMBER_ID
GROUP BY A.MEMBER_NAME
ORDER BY REVIEW_SCORE DESC;

 

수정결과

 

 

<오류3>

SELECT T1.MEMBER_NAME, T1.REVIEW_TEXT, TO_CHAR(T1.REVIEW_DATE, 'YYYY-MM-DD') AS REVIEW_DATE
FROM (
    SELECT A.MEMBER_ID, A.MEMBER_NAME, B.REVIEW_TEXT, B.REVIEW_DATE, B.REVIEW_SCORE
    FROM MEMBER_PROFILE A
    INNER JOIN REST_REVIEW B ON A.MEMBER_ID = B.MEMBER_ID
    WHERE (B.MEMBER_ID, B.REVIEW_SCORE) IN (
        SELECT MEMBER_ID, MAX(REVIEW_SCORE) AS MAX_REVIEW_SCORE
        FROM REST_REVIEW
        GROUP BY MEMBER_ID
    )
) T1
ORDER BY T1.REVIEW_DATE, T1.REVIEW_TEXT;

 

 

- WHRER IN 사용법

SELECT MEMBER_ID, MAX(REVIEW_SCORE) AS MAX_REVIEW_SCORE
FROM REST_REVIEW
GROUP BY MEMBER_ID

 

WHERE IN

메인 쿼리에서는 **B.MEMBER_ID**와 **B.REVIEW_SCORE**의 조합이 1.서브쿼리의 결과와 일치하는지 확인하며, 일치하는 경우 해당 리뷰 정보를 결과에 포함

 

 

 

<정답>

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 3, 2;