#68. JOIN : 주문량이 많은 아이스크림들 조회하기 | 프로그래머스 | SQL 고득점 Kit

문제

 

입력 코드

1. JOIN - ON

SELECT FH.FLAVOR
FROM FIRST_HALF AS FH
    JOIN JULY AS JUL
    ON FH.FLAVOR = JUL.FLAVOR
GROUP BY FH.FLAVOR
ORDER BY SUM(FH.TOTAL_ORDER) + SUM(JUL.TOTAL_ORDER) DESC
LIMIT 3;

 

2. JOIN - USING

SELECT FLAVOR
FROM FIRST_HALF AS A
JOIN JULY AS B 
    USING (FLAVOR)
GROUP BY FLAVOR
ORDER BY SUM(A.TOTAL_ORDER + B.TOTAL_ORDER) DESC
LIMIT 3;

→ 조인할 두 컬럼의 이름이 같은 경우에는 ON 대신에 USING을 사용할 수 있음

 

 

3. SUBQUERY

SELECT FH.FLAVOR
FROM FIRST_HALF AS FH
    JOIN (SELECT FLAVOR, TOTAL_ORDER 
         FROM JULY) AS JUL
    ON FH.FLAVOR = JUL.FLAVOR
GROUP BY FH.FLAVOR
ORDER BY SUM(FH.TOTAL_ORDER) + SUM(JUL.TOTAL_ORDER) DESC
LIMIT 3;

 

4. SUBQUERY

SELECT FH.FLAVOR
FROM FIRST_HALF AS FH
    JOIN (SELECT FLAVOR, SUM(TOTAL_ORDER)
          AS TOTAL_ORDER
          FROM JULY
          GROUP BY FLAVOR) AS JUL_TOTAL
    ON FH.FLAVOR = JUL_TOTAL.FLAVOR
ORDER BY (FH.TOTAL_ORDER + JUL_TOTAL.TOTAL_ORDER) DESC
LIMIT 3;

 

5. UNION

SELECT FLAVOR
FROM
    (SELECT *
     FROM FIRST_HALF
     UNION
     SELECT *
     FROM JULY) AS a
GROUP BY FLAVOR
ORDER BY SUM(TOTAL_ORDER) DESC
LIMIT 3;

 

 

코드 설명

#SELECT #JOIN #GROUP BY #ORDER BY #SUM #LIMIT #SUBQUERY #UNION

 

 

문제 출처

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