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

프로그래머스

 

전체코드

WITH POWER AS (
    SELECT COUNT(A.MEMBER_ID) AS CNT, A.MEMBER_ID, B.MEMBER_NAME
    FROM REST_REVIEW A LEFT JOIN MEMBER_PROFILE B ON A.MEMBER_ID = B.MEMBER_ID
    GROUP BY A.MEMBER_ID
    ORDER BY CNT DESC LIMIT 1
)

SELECT B.MEMBER_NAME, A.REVIEW_TEXT, DATE_FORMAT( A.REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM REST_REVIEW A JOIN POWER B ON A.MEMBER_ID = B.MEMBER_ID
ORDER BY A.REVIEW_DATE, A.REVIEW_TEXT;

 

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