SQL 코드카타 : 66번~68번(CONCAT, UNION, 처음보는(오라클식))

 

문제 66. 조회수가 가장 높은 게시물의 경로를 CONCAT으로 생성하기

 

작성 코드🔫 탕탕

SELECT CONCAT('/home/grep/src/', UB.BOARD_ID, '/', FILE_ID, FILE_NAME, FILE_EXT) AS FILE_PATH
FROM USED_GOODS_BOARD AS UB
JOIN USED_GOODS_FILE AS UF
ON UB.BOARD_ID=UF.BOARD_ID
WHERE UB.BOARD_ID =
(
SELECT BOARD_ID
FROM USED_GOODS_BOARD
GROUP BY BOARD_ID
ORDER BY VIEWS DESC
LIMIT 1
)
ORDER BY FILE_PATH DESC

 

🕒실행 결과

 

 

✏️참고 코드

SELECT CONCAT(CONCAT_WS("/", "/home/grep/src", F.BOARD_ID, F.FILE_ID), F.FILE_NAME, F.FILE_EXT) AS FILE_PATH
FROM USED_GOODS_BOARD AS B
JOIN USED_GOODS_FILE AS F
ON B.BOARD_ID = F.BOARD_ID
WHERE B.VIEWS =
(
SELECT MAX(VIEWS)
FROM USED_GOODS_BOARD
)
ORDER BY F.FILE_ID DESC

 

: 서브 쿼리문의 차이

 

문제 67. 컬럼이 같은 두 테이블을 UNION하기

 

작성 코드 (WITH 없는 UNION)

SELECT FLAVOR
FROM
(
SELECT FLAVOR, SUM(TOTAL_ORDER) AS TOTAL
FROM JULY
GROUP BY FLAVOR

UNION ALL

SELECT FLAVOR, SUM(TOTAL_ORDER)
FROM FIRST_HALF
GROUP BY FLAVOR
) TOTAL
GROUP BY FLAVOR
ORDER BY SUM(TOTAL) DESC
LIMIT 3

 

작성 코드 (가독성UP 확인)

SELECT FLAVOR
FROM (SELECT FLAVOR, SUM(TOTAL_ORDER) AS TOTAL
      FROM JULY
      GROUP BY FLAVOR
      UNION ALL
      SELECT FLAVOR, SUM(TOTAL_ORDER)
      FROM FIRST_HALF
      GROUP BY FLAVOR) AS TOTAL
GROUP BY FLAVOR
ORDER BY SUM(TOTAL) DESC
LIMIT 3

 

🕒서브 쿼리문 실행 결과 (UNION ALL: 중복된 값 살리기)

 

🔎LEFT JOIN만 배웠다면?

WITH SUB AS (SELECT FH.FLAVOR,
             SUM(FH.TOTAL_ORDER)+SUM(JU.TOTAL_ORDER) TOTAL_ORDER
             FROM FIRST_HALF AS FH
             LEFT JOIN JULY AS JU ON FH.FLAVOR = JU.FLAVOR
             GROUP BY FH.FLAVOR)
             
SELECT FLAVOR
FROM SUB
ORDER BY TOTAL_ORDER DESC
LIMIT 3

 

🔎WITH, UNION을 같이쓴다면?

WITH A AS (SELECT FLAVOR, TOTAL_ORDER
           FROM FIRST_HALF
           UNION ALL
           SELECT FLAVOR, TOTAL_ORDER
           FROM JULY)

SELECT FLAVOR
FROM A
GROUP BY FLAVOR
ORDER BY SUM(TOTAL_ORDER) DESC
LIMIT 3

 

🕒실행 결과

 

문제 68. WITH, JOIN을 이용하여 세 테이블을 각각 그룹화

 

작성 코드(서브쿼리문: 1.날짜조건 2.저자별 3.카테고리별 4.매출)

WITH A AS (SELECT AUTHOR_ID, CATEGORY, SUM(PRICE*SALES) AS SALES
           FROM BOOK BOK
           JOIN BOOK_SALES SAL
           ON BOK.BOOK_ID=SAL.BOOK_ID
           WHERE DATE_FORMAT(SALES_DATE, '%Y-%m') = '2022-01'
           GROUP BY AUTHOR_ID, CATEGORY)

SELECT A.AUTHOR_ID, AUTHOR_NAME, CATEGORY, SALES
FROM A LEFT JOIN AUTHOR AUT ON A.AUTHOR_ID=AUT.AUTHOR_ID
ORDER BY A.AUTHOR_ID, CATEGORY DESC

 

🕒서브쿼리문 & 메인 쿼리문 실행결과

(좌) 서브쿼리문 (우) 메인쿼리문

 

✏️참고 코드

select a.author_id, b.author_name, a.category, sum(a.price*c.sales) as total_sales
from book a, author b, book_sales c
where a.author_id = b.author_id
and a.book_id = c.book_id
and c.sales_date like '2022-01%'
group by a.author_id, a.category
order by a.author_id, a.category desc;

 

💡JOIN이 아니라 "WHERE AND 조건절"을 이용해서 JOIN과 같은 기능으로 작동하는 쿼리문이다.