[Oracle] 주문량이 많은 아이스크림들 조회하기

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

프로그래머스

정답

SELECT FLAVOR
FROM(
    SELECT ROWNUM RN,T.*
    FROM (
        SELECT A.FLAVOR, SUM(B.TOTAL_ORDER)+A.TOTAL_ORDER TOTAL
        FROM FIRST_HALF A
            INNER JOIN JULY B
            ON A.FLAVOR= B.FLAVOR
        GROUP BY A.FLAVOR,A.TOTAL_ORDER
        ORDER BY TOTAL DESC
    ) T
)R
WHERE R.RN <=3;

풀이

Step 1) from 절 group by 

SELECT  A.FLAVOR,A.TOTAL_ORDER as A테이블_TOTAL_ORDER,B.TOTAL_ORDER as B테이블_TOTAL_ORDER
FROM FIRST_HALF A
    INNER JOIN JULY B
    ON A.FLAVOR= B.FLAVOR

• A테이블 TOTAL_ORDER 컬럼은 값이 동일하기 때문에 GROUP BY 안함

 SELECT A.FLAVOR, SUM(B.TOTAL_ORDER)+A.TOTAL_ORDER TOTAL
FROM FIRST_HALF A
    INNER JOIN JULY B
    ON A.FLAVOR= B.FLAVOR
GROUP BY A.FLAVOR,A.TOTAL_ORDER
ORDER BY TOTAL DESC

Step 2) ROWNUM

SELECT ROWNUM RN,T.*
    FROM (
        SELECT A.FLAVOR, SUM(B.TOTAL_ORDER)+A.TOTAL_ORDER TOTAL
        FROM FIRST_HALF A
            INNER JOIN JULY B
            ON A.FLAVOR= B.FLAVOR
        GROUP BY A.FLAVOR,A.TOTAL_ORDER
        ORDER BY TOTAL DESC
    ) T

Step 3) ROWNUM 조건 <=3 

SELECT FLAVOR
FROM(
    SELECT ROWNUM RN,T.*
    FROM (
        SELECT A.FLAVOR, SUM(B.TOTAL_ORDER)+A.TOTAL_ORDER TOTAL
        FROM FIRST_HALF A
            INNER JOIN JULY B
            ON A.FLAVOR= B.FLAVOR
        GROUP BY A.FLAVOR,A.TOTAL_ORDER
        ORDER BY TOTAL DESC
    ) T
)R
WHERE R.RN <=3;