#03. SELECT : 조건에 부합하는 중고거래 댓글 조회하기 | 프로그래머스 | SQL 고득점 Kit

문제

 

입력 코드

1. AND

SELECT 
    board.TITLE, board.BOARD_ID, 
    reply.REPLY_ID, reply.WRITER_ID, reply.CONTENTS, 
    DATE_FORMAT(reply.CREATED_DATE, '%Y-%m-%d') AS CREATED_DATE
FROM USED_GOODS_BOARD as board
    JOIN USED_GOODS_REPLY AS reply
    ON board.BOARD_ID = reply.BOARD_ID
WHERE (board.CREATED_DATE >= '2022-10-01') 
    AND (board.CREATED_DATE <= '2022-10-31')
ORDER BY reply.CREATED_DATE ASC, board.TITLE ASC;

 

2. SUBSTRING

SELECT  
    A.TITLE, A.BOARD_ID, B.REPLY_ID, B.WRITER_ID, B.CONTENTS, 
    DATE_FORMAT(B.CREATED_DATE, '%Y-%m-%d') AS CRAETED_DATE
FROM USED_GOODS_BOARD AS A
    INNER JOIN  USED_GOODS_REPLY AS B
    ON A.BOARD_ID = B.BOARD_ID
WHERE SUBSTRING(A.CREATED_DATE,1,7) = '2022-10'
ORDER BY B.CREATED_DATE ASC, A.TITLE ASC;

 

3. DATE_FORMAT

SELECT 
    TITLE, BOARD.BOARD_ID, REPLY_ID, REPLY.WRITER_ID, REPLY.CONTENTS, 
    DATE_FORMAT(REPLY.CREATED_DATE, '%Y-%m-%d') AS CREATED_DATE
FROM USED_GOODS_REPLY AS REPLY
    LEFT JOIN USED_GOODS_BOARD AS BOARD
    ON REPLY.BOARD_ID=BOARD.BOARD_ID
WHERE DATE_FORMAT(BOARD.CREATED_DATE, '%Y-%m') = '2022-10'
ORDER BY REPLY.CREATED_DATE, TITLE

 

4. MONTH

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
    JOIN USED_GOODS_REPLY AS R
    ON B.BOARD_ID = R.BOARD_ID

WHERE MONTH(B.CREATED_DATE) = 10
ORDER BY R.CREATED_DATE ASC, B.TITLE ASC

→ 게시글 작성연도가 모두 2022년이므로 작성일이 10월인 것으로 조회

 

 

코드 설명

#SELECT #DATE_FORMAT #JOIN #WHERE #ORDER BY #SUBSTRING

 

 

문제 출처

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