[프로그래머스 스쿨 - MYSQL] 중고거래 문제 Lv.1 ~Lv.3

LV.1 조건에 부합하는 중고거래 댓글 조회하기 

문제

USED_GOODS_BOARD와 USED_GOODS_REPLY 테이블에서 2022년 10월에 작성된 게시글 제목, 게시글 ID, 댓글 ID, 댓글 작성자 ID, 댓글 내용, 댓글 작성일을 조회하는 SQL문을 작성해주세요. 결과는 댓글 작성일을 기준으로 오름차순 정렬해주시고, 댓글 작성일이 같다면 게시글 제목을 기준으로 오름차순 정렬해주세요.

출력

해설

추출해야할 것 TITLE, BOARD_ID, REPLY_ID, WRITER_ID, CONTENTS, CREATED_DATE<년월일만 나오게>

조건 2022년 10월

정렬 댓글 작성일 오름차순, 게시글 제목 오름차순

 

1.USED_GOODS_BOARD 테이블과 USED_GOODS_REPLY에 대한 필드를 가져와야하기 때문에

BOARD_ID라는 기본키를 이용해 INNER JOIN을 한다

2. WRITER_ID와 BOARD_ID, CREATED_DATE를 가져올 때, 어떤 테이블에서 가져왔는지 명명해줘야 한다

<여기서 낚시는 CREATED_DATE 조건이 USED_GOODS_BOARD의 것이지 USED_GOODS_REPLY 테이블이 아니다>

 

이를 구현한 코드는 아래와 같다

SELECT B.TITLE, B.BOARD_ID, R.REPLY_ID, R.WRITER_ID, R.CONTENTS, 
DATE_FORMAT(R.CREATED_DATE, "%Y-%m-%d") AS CREATED_DATE
FROM USED_GOODS_BOARD AS B INNER JOIN USED_GOODS_REPLY AS R ON B.BOARD_ID = R.BOARD_ID
WHERE B.CREATED_DATE BETWEEN "2022-10-01" AND "2022-10-31"
ORDER BY R.CREATED_DATE ASC, B.TITLE ASC

 

LV.2 조건에 부합하는 중고거래 상태 조회하기 

문제

 USED_GOODS_BOARD 테이블에서 2022년 10월 5일에 등록된 중고거래 게시물의 게시글 ID, 작성자 ID, 게시글 제목, 가격, 거래상태를 조회하는 SQL문을 작성해주세요. 거래상태가 SALE 이면 판매중, RESERVED이면 예약중, DONE이면 거래완료 분류하여 출력해주시고, 결과는 게시글 ID를 기준으로 내림차순 정렬해주세요.

 

해설

추출해야할 것  BOARD_ID, WRITER_ID, TITLE, PRICE, STATUS<조건에 맞게>

조건 2022년 10월 5일자 게시글, SALE -> 판매중, RESERVED -> 예약중, DONE -> 거래완료

정렬 게시글 ID 내림차순

 

1. CASE ~ WHEN THEN ~ END 구문으로 조건에 맞는 필드를 변형하여 출력한다

2. 조건과 내림차순을 잘 확인한다

 

이를 구현한 코드는 아래와 같다. 가독성은 꼭 챙겨주기 

SELECT BOARD_ID, WRITER_ID, TITLE, PRICE, 
CASE
    WHEN STATUS = "SALE" THEN "판매중"
    WHEN STATUS = "RESERVED" THEN "예약중"
    WHEN STATUS = "DONE" THEN "거래완료" 
END AS "STATUS"
FROM USED_GOODS_BOARD
WHERE CREATED_DATE = "2022-10-05"
ORDER BY BOARD_ID DESC

 

LV.3 조건에 부합하는 중고거래 상태 조회하기 

문제

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

 

해설

추출해야할 것  /home/grep/src/B0001/IMG_000001photo1.jpg 형태의 FILE_PATH

조건  최고 조회수 게시글

정렬 FILE_ID 내림차순

 

1. TABLE JOIN을 어떻게 할지 생각한다. 

조건은 USED_GOODS_BOARD에서 판별하고 조회는 USED_GOODS_FILE에서 한다

2. 최다 조회수를 추출하는 조건문을 작성한다

3. 형식에 알맞게 문자열을 출력한다 <CONCAT> 사용

 

이 문제는 테이블을 어떻게 JOIN하는가에 따라 속도 차이가 날 수도 있다.

그래서 3가지 버전으로 풀어볼 수 있다. <방법은 무궁무진하다>

-- 테이블 전체 JOIN 할 때, 리소스를 많이 잡아먹을 수도 있다
SELECT CONCAT("/home/grep/src/", F.BOARD_ID,'/', F.FILE_ID, F.FILE_NAME, F.FILE_EXT ) AS "FILE_PATH"
FROM USED_GOODS_BOARD AS B INNER JOIN USED_GOODS_FILE AS F ON B.BOARD_ID = F.BOARD_ID
WHERE B.VIEWS = (SELECT MAX(B.VIEWS) FROM USED_GOODS_BOARD AS B )
ORDER BY F.FILE_ID DESC

-- JOIN할 테이블에서 조건에 맞는 행만 가져올 떄, 전체 테이블 조회보다 훨씬 낫다
SELECT CONCAT("/home/grep/src/", F.BOARD_ID,'/', F.FILE_ID, F.FILE_NAME, F.FILE_EXT ) AS "FILE_PATH"
FROM (SELECT * FROM USED_GOODS_BOARD ORDER BY VIEWS DESC LIMIT 1) AS B
INNER JOIN USED_GOODS_FILE AS F ON B.BOARD_ID = F.BOARD_ID
WHERE B.VIEWS = (SELECT MAX(B.VIEWS) FROM USED_GOODS_BOARD AS B )
ORDER BY F.FILE_ID DESC

-- 서브쿼리 두 개 쓸 때, 코드가 간단하다, 범용성은 조금 떨어질 수 있다
SELECT CONCAT("/home/grep/src/", F.BOARD_ID,'/', F.FILE_ID, F.FILE_NAME, F.FILE_EXT ) AS "FILE_PATH"
FROM USED_GOODS_FILE AS F
WHERE F.BOARD_ID = (SELECT BOARD_ID FROM USED_GOODS_BOARD
                   WHERE VIEWS = (SELECT MAX(VIEWS) FROM USED_GOODS_BOARD))
ORDER BY F.FILE_ID DESC

 

 

문제 링크

https://school.programmers.co.kr/learn/challenges?order=recent&page=1&languages=mysql  

코딩테스트 연습 | 프로그래머스 스쿨