join문을 서브쿼리로 풀어보자 1

31. 오랜기간 보호한 동물 (1)

문제 링크:https://school.programmers.co.kr/learn/courses/30/lessons/59044

정답 코드:

SELECT
    ai.NAME,
    ai.DATETIME    
FROM  ANIMAL_INS ai
LEFT JOIN ANIMAL_OUTS ao
    on ai.ANIMAL_ID = ao.ANIMAL_ID
WHERE ao.DATETIME IS NULL
ORDER BY DATETIME
LIMIT 3;

 

다르게 푼 코드:

SELECT NAME, DATETIME
FROM ANIMAL_INS 
WHERE ANIMAL_ID NOT IN (
        SELECT ANIMAL_ID
        FROM ANIMAL_OUTS)
ORDER BY DATETIME ASC
LIMIT 3;

 

문제 풀이때 했던 생각.

# 아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물  # 3마리의 # 이름과 보호 시작일 # 결과는 보호 시작일 순 필요한 요소 ANIMAL_OUTS에 없다는 것 자체가 입양을 아직 가지 못했다는 의미. 때문에 ANIMAL_INS의 ANIMAL_ID가 ANIMAL_OUTS의 ANIMAL_ID에 없다면 입양을 가지 못함을 의미한다. WHERE 서브쿼리를 이용해 필터링 (단일값이 아니기 때문에 IN 사용)

 

---

32. 카테고리 별 도서 판매량 집계하기

문제 링크: https://school.programmers.co.kr/learn/courses/30/lessons/144855

정답 코드:

SELECT
    b.CATEGORY,
    SUM(bs.SALES) AS TOTAL_SALES
FROM BOOK b
LEFT JOIN BOOK_SALES bs
    on b.BOOK_ID = bs.BOOK_ID
WHERE bs.SALES_DATE LIKE '2022-01%'
GROUP BY b.CATEGORY
ORDER BY CATEGORY ASC

 

다르게 푼 코드

SELECT b.CATEGORY, SUM(s.SALES) AS TOTAL_SALES
FROM BOOK b
INNER JOIN (
    SELECT 
    BOOK_ID,
    SALES
    FROM BOOK_SALES
    WHERE SALES_DATE LIKE '2022-01%') as s
ON b.BOOK_ID = s.BOOK_ID
GROUP BY b.CATEGORY

문제 풀이때 했던 생각.

# 2022년 1월의 카테고리 별 도서 판매량을 합산 # 카테고리(CATEGORY), 총 판매량(TOTAL_SALES) 리스트를 출력 # 결과는 카테고리명을 기준으로 오름차순 정렬 필요한 데이터는? 2022년 1월의 카테고리 별 도서 판매량 출력할 값 카테고리, 판매량 ' 카테고리' 별로 묶였기 때문에 group by는  CATEGORY로

 

---

 

33. 상품 별 오프라인 매출 구하기

문제 링크: https://school.programmers.co.kr/learn/courses/30/lessons/131533

정답 코드:

SELECT
    p.PRODUCT_CODE,
    SUM(p.PRICE * os.SALES_AMOUNT) AS SALES
FROM PRODUCT p
INNER JOIN OFFLINE_SALE os
    on p.PRODUCT_ID = os.PRODUCT_ID
GROUP BY p.PRODUCT_CODE
ORDER BY SALES DESC, p.PRODUCT_CODE ASC;

 

 

다르게 푼 코드

SELECT
    p.PRODUCT_CODE,
    SUM(p.PRICE* sa.SALES_AMOUNT) AS 'SALES'
FROM PRODUCT p
INNER JOIN(
    SELECT PRODUCT_ID, SALES_AMOUNT
    FROM OFFLINE_SALE 
    ) AS sa
    on p.PRODUCT_ID = sa.PRODUCT_ID
GROUP BY p.PRODUCT_CODE
ORDER BY SALES DESC, p.PRODUCT_CODE ASC;

 

문제 풀이때 했던 생각.

# 상품코드 별 매출액(판매가 * 판매량) 합계 # 결과는 매출액을 기준으로 내림차순 정렬 매출액이 같다면 상품코드를 기준으로 오름차순 그럼 필요한 값 상품코드 별 매출액( 판매가 * 판매량) 판매량은 PRODUCT에 없기 때문에, OFFLINE_SALE 테이블에서 가져와야함. 서브쿼리로 PRODUCT_ID, SALES_AMOUNT를 출력해 메인쿼리와 join LEFT JOIN 안쓴 이유. 해당 문제에서 원하는것은  상품코드 별 매출액. 즉, 판매된 제품들에 대한 매출액을 원한다. 판매되지 않았더라면  OFFLINE_SALE에 NULL을 가지고 있을것이다. LEFT JOIN 은 기준 테이블을 모두 출력하기 때문에   OFFLINE_SALE이 NULL인 값을 올릴수도 있기 때문에 사용하지 않았다.

 

---

39.  성분으로 구분한 아이스크림 총 주문량

문제 링크:https://school.programmers.co.kr/learn/courses/30/lessons/133026

정답 코드:

SELECT
    ii.INGREDIENT_TYPE,
    SUM(fh.TOTAL_ORDER) AS TOTAL_ORDER
FROM ICECREAM_INFO ii
INNER JOIN FIRST_HALF fh
    on ii.FLAVOR = fh.FLAVOR
GROUP BY ii.INGREDIENT_TYPE
ORDER BY TOTAL_ORDER ASC

 

다르게 푼 코드

SELECT 
    INGREDIENT_TYPE, 
    SUM(f.TOTAL_ORDER) as TOTAL_ORDER
FROM ICECREAM_INFO i
INNER JOIN (
    SELECT TOTAL_ORDER, FLAVOR
    FROM FIRST_HALF) as f
ON i.FLAVOR = f.FLAVOR
GROUP BY i.INGREDIENT_TYPE
ORDER BY TOTAL_ORDER ASC

 

문제 풀이때 했던 생각.

각 아이스크림 성분 타입과 성분 타입에 대한 아이스크림의 총주문량 총주문량을 나타내는 컬럼명은 TOTAL_ORDER 총주문량이 작은 순서대로 조회(오름차순) # 필요한것 아이스크림 성분 타입, 성분 타입에 대한 아이스크림의 총주문량

무엇이 잘못되었나?

틀린건 아니지만 미숙하게 품! FIRST_HALF를 그대로 가져왔으나 만약, FLAVOR별로 미리 집계하면 구조가 더 또렷해진다.   서브쿼리를  서브쿼리에서 의미 있는 단위로 정제했기에  메인 쿼리는 “재료 타입별 합계”에만 집중할수 있게 된다.

SELECT
    i.INGREDIENT_TYPE,
    SUM(f.TOTAL_ORDER) AS TOTAL_ORDER
FROM ICECREAM_INFO i
JOIN (
    SELECT
        FLAVOR,
        SUM(TOTAL_ORDER) AS TOTAL_ORDER
    FROM FIRST_HALF
    GROUP BY FLAVOR
) f
ON i.FLAVOR = f.FLAVOR
GROUP BY i.INGREDIENT_TYPE
ORDER BY TOTAL_ORDER ASC;

---

https://papago9211.tistory.com/79

코드카타 Level 3 1번~3번

 

https://papago9211.tistory.com/82

코드카타 Level 3 34~38 (sql 복습시 참고) -나중에 서브쿼리로 풀어보기-

 

 

서브쿼리에 익숙해지기 위해서 하는 풀이기 때문에 항상 문제를 풀때에도 join, 서브쿼리로 생각해 문제를 풀어보기