WITH절 문제 예시

문제 예시

 

다음은 중고거래 게시판 정보를 담은 USED_GOODS_BOARD 테이블과 중고거래 게시판 첨부파일 정보를 담은 USED_GOODS_FILE 테이블입니다. USED_GOODS_BOARD 테이블은 다음과 같으며 BOARD_ID, WRITER_ID, TITLE, CONTENTS, PRICE, CREATED_DATE, STATUS, VIEWS은 게시글 ID, 작성자 ID, 게시글 제목, 게시글 내용, 가격, 작성일, 거래상태, 조회수를 의미합니다.

 

Column nameTypeNullable

BOARD_ID	VARCHAR(5)	FALSE
WRITER_ID	VARCHAR(50)	FALSE
TITLE	VARCHAR(100)	FALSE
CONTENTS	VARCHAR(1000)	FALSE
PRICE	NUMBER	FALSE
CREATED_DATE	DATE	FALSE
STATUS	VARCHAR(10)	FALSE
VIEWS	NUMBER	FALSE

USED_GOODS_FILE 테이블은 다음과 같으며 FILE_ID, FILE_EXT, FILE_NAME, BOARD_ID는 각각 파일 ID, 파일 확장자, 파일 이름, 게시글 ID를 의미합니다.

Column nameTypeNullable

FILE_ID	VARCHAR(10)	FALSE
FILE_EXT	VARCHAR(5)	FALSE
FILE_NAME	VARCHAR(256)	FALSE
BOARD_ID	VARCHAR(10)	FALSE

 

---

문제

USED_GOODS_BOARD와 USED_GOODS_FILE 테이블에서 조회수가 가장 높은 중고거래 게시물에 대한 첨부파일 경로를 조회하는 SQL문을 작성해주세요. 첨부파일 경로는 FILE ID를 기준으로 내림차순 정렬해주세요. 기본적인 파일경로는 /home/grep/src/ 이며, 게시글 ID를 기준으로 디렉토리가 구분되고, 파일이름은 파일 ID, 파일 이름, 파일 확장자로 구성되도록 출력해주세요. 조회수가 가장 높은 게시물은 하나만 존재합니다.

 

MYSQL 

WITH A AS (SELECT
BOARD_ID
FROM
USED_GOODS_BOARD
ORDER BY VIEWS DESC
LIMIT 1)

SELECT
CONCAT("/home/grep/src/", BOARD_ID, "/", FILE_ID, FILE_NAME, FILE_EXT) AS FILE_PATH
FROM
USED_GOODS_FILE
WHERE BOARD_ID = (SELECT * FROM A)
ORDER BY FILE_ID DESC

 

주어진 SQL 쿼리는 두 개의 SQL쿼리를 결합하여 특정 게시판의 파일 경로를 조회하는 작업을 수행한다. 

1. 서브쿼리 A 를 사용하여 가장 조회수가 많은 게시판을 찾는다.

2. 메인 쿼리에서 이 게시판에 관련된 파일의 경로를 조회한다.

 

서브쿼리 'A'

WITH A AS (
    SELECT
        BOARD_ID
    FROM
        USED_GOODS_BOARD
    ORDER BY VIEWS DESC
    LIMIT 1
)

설명:

• WITH A AS (...): 이 구문은 공통 테이블 표현식(Common Table Expression, CTE)을 정의. CTE는 쿼리에서 반복적으로 사용할 수 있는 임시 결과 집합을 정의한다.
• SELECT BOARD_ID FROM USED_GOODS_BOARD: USED_GOODS_BOARD 테이블에서 BOARD_ID를 선택
• ORDER BY VIEWS DESC: 게시판을 조회수(VIEWS) 기준으로 내림차순 정렬. 가장 조회수가 높은 게시판이 가장 위에 위치한다
• LIMIT 1: 조회수가 가장 높은 게시판 하나만 선택한다.

이 서브쿼리는 조회수가 가장 높은 게시판의 BOARD_ID를 반환한다.

 

메인 쿼리

SELECT
    CONCAT("/home/grep/src/", BOARD_ID, "/", FILE_ID, FILE_NAME, FILE_EXT) AS FILE_PATH
FROM
    USED_GOODS_FILE
WHERE BOARD_ID = (SELECT * FROM A)
ORDER BY FILE_ID DESC

 

설명:

SELECT CONCAT("/home/grep/src/", BOARD_ID, "/", FILE_ID, FILE_NAME, FILE_EXT) AS FILE_PATH:

• CONCAT 함수는 문자열을 결합
• 경로는 "/home/grep/src/" + BOARD_ID + "/" + FILE_ID + FILE_NAME + FILE_EXT 형식으로 생성됩니다.
• 이 경로는 FILE_PATH라는 별칭으로 선택된다.

FROM USED_GOODS_FILE: USED_GOODS_FILE 테이블에서 데이터를 가져온다.

WHERE BOARD_ID = (SELECT * FROM A):

• WHERE 절은 서브쿼리 A에서 반환된 BOARD_ID와 일치하는 파일만 선택한다.
• SELECT * FROM A는 A CTE에서 BOARD_ID를 가져옵니다. 이 서브쿼리는 A의 결과, 즉 조회수가 가장 높은 게시판의 BOARD_ID를 반환한다.

ORDER BY FILE_ID DESC:

• 선택된 파일들을 FILE_ID 기준으로 내림차순으로 정렬한다. 즉, FILE_ID가 가장 큰 파일이 가장 위에 위치한다.

순서

1. 가장 조회수가 높은 게시판의 BOARD_ID를 서브쿼리 A에서 찾음
2. 그 게시판에 관련된 파일의 경로를 USED_GOODS_FILE 테이블에서 찾음
3. 파일 경로는 /home/grep/src/" + BOARD_ID + "/" + FILE_ID + FILE_NAME + FILE_EXT 형식으로 생성
4. 파일을 FILE_ID 기준으로 내림차순으로 정렬, 파일 ID가 큰 순서대로 나열된 결과를 얻을 수 있음