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

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

 

 

 

CTE - WITH 절을 이용해서 임시 테이블 만들기

WITH CTE AS (select a.MEMBER_NAME
     , count(*) as review_cnt
from MEMBER_PROFILE a
        JOIN
     REST_REVIEW b ON a.MEMBER_ID = b.MEMBER_ID
group by a.MEMBER_NAME),

CTE2 AS (select a.MEMBER_NAME,
                b.REVIEW_TEXT,
                b.REVIEW_DATE,
                c.review_cnt
from MEMBER_PROFILE a
        JOIN
     REST_REVIEW b ON a.MEMBER_ID = b.MEMBER_ID
        JOIN
     CTE c ON a.MEMBER_NAME = c.MEMBER_NAME)

SELECT MEMBER_NAME
     , REVIEW_TEXT
     , DATE_FORMAT(REVIEW_DATE, '%Y-%m-%d')
FROM CTE2 a
       LEFT JOIN
    (select MAX(review_cnt) as max_cnt
    from CTE2) b ON a.review_cnt = b.max_cnt
WHERE max_cnt is NOT NULL
ORDER BY REVIEW_DATE, REVIEW_TEXT


--출처 : https://hmm06.tistory.com/103

 

 

RANK() 함수 사용하기

WITH MEMBER_RANK AS (
    SELECT MEMBER_ID, RANK() OVER (ORDER BY COUNT(MEMBER_ID) DESC) RNK
    FROM REST_REVIEW
    GROUP BY MEMBER_ID 
)
SELECT MEMBER_NAME,	REVIEW_TEXT, DATE_FORMAT(REVIEW_DATE, '%Y-%m-%d') REVIEW_DATE
FROM MEMBER_RANK RA JOIN MEMBER_PROFILE P 
ON RA.MEMBER_ID = P.MEMBER_ID 
JOIN REST_REVIEW R ON R.MEMBER_ID = P.MEMBER_ID 
WHERE RA.RNK = 1
ORDER BY 3, 2

-- 출처: https://20240228.tistory.com/269