Day49

코드카타 - sql

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

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

특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

• 첫 풀이

SET @SEDAN = (
    SELECT (100 - DISCOUNT_RATE) / 100
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN 
    WHERE CAR_TYPE LIKE '세단'
    AND DURATION_TYPE LIKE '30일 이상'
);

SET @SUV = (
    SELECT (100 - DISCOUNT_RATE) / 100
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN 
    WHERE CAR_TYPE LIKE 'SUV'
    AND DURATION_TYPE LIKE '30일 이상'
);

WITH HISTORY AS (
    SELECT CAR_ID, START_DATE, END_DATE
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    GROUP BY CAR_ID
    HAVING SUM(CASE WHEN '2022-11-01' BETWEEN START_DATE AND END_DATE THEN 1
                   WHEN '2022-11-30' BETWEEN START_DATE AND END_DATE THEN 1
                   WHEN START_DATE BETWEEN '2022-11-01' AND '2022-11-30' THEN 1
                   WHEN END_DATE BETWEEN '2022-11-01' AND '2022-11-30' THEN 1
                   ELSE 0
              END) = 0
), CAR AS (
    SELECT CAR_ID, CAR_TYPE,
        CASE WHEN CAR_TYPE = '세단' THEN ROUND(30 * DAILY_FEE * @SEDAN, 0) 
             WHEN CAR_TYPE = 'SUV' THEN ROUND(30 * DAILY_FEE * @SUV, 0) 
             ELSE 0
        END AS FEE
    FROM CAR_RENTAL_COMPANY_CAR
    WHERE CAR_TYPE LIKE 'SUV'
    OR CAR_TYPE LIKE '세단'
)

SELECT DISTINCT A.CAR_ID, A.CAR_TYPE, A.FEE
FROM CAR A
JOIN HISTORY B
ON A.CAR_ID = B.CAR_ID
WHERE A.FEE >=500000 AND A.FEE < 2000000
ORDER BY 3 DESC, 2 ASC, 1 DESC
;

• 수정된 풀이

WITH CAR AS (
    SELECT A.CAR_ID, A.CAR_TYPE, A.DAILY_FEE, 
        ROUND(30 * A.DAILY_FEE * (100 - B.DISCOUNT_RATE) / 100, 0) AS FEE
    FROM CAR_RENTAL_COMPANY_CAR A
    JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN B
    ON A.CAR_TYPE = B.CAR_TYPE
    WHERE (A.CAR_TYPE LIKE 'SUV'
    OR A.CAR_TYPE LIKE '세단')
    AND B.DURATION_TYPE LIKE '30일 이상'
)

SELECT DISTINCT A.CAR_ID, A.CAR_TYPE,A.FEE
FROM CAR A
LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY B
ON A.CAR_ID = B.CAR_ID
WHERE A.FEE >= 500000 AND A.FEE < 2000000
GROUP BY A.CAR_ID
HAVING SUM(CASE WHEN '2022-11-01' BETWEEN B.START_DATE AND B.END_DATE THEN 1
                WHEN '2022-11-30' BETWEEN B.START_DATE AND B.END_DATE THEN 1
                WHEN B.START_DATE BETWEEN '2022-11-01' AND '2022-11-30' THEN 1
               # WHEN B.END_DATE BETWEEN '2022-11-01' AND '2022-11-30' THEN 1
                ELSE 0
           END) = 0
ORDER BY 3 DESC, 2 ASC, 1 DESC
;

• 첫 풀이의 경우 최대한 SET과 WITH를 사용하여 보기 좋게 나누려고 하였지만, 이 경우에는 대여 기록이 없는 차량의 경우에 대여할 수 있음에도 리스트에서 제외되고 만다. 그래서 아래 풀이로 수정하였다.

자동차 대여 기록 별 대여 금액 구하기

SET @SEVEN = (
    SELECT (100 - DISCOUNT_RATE) / 100
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN 
    WHERE CAR_TYPE LIKE '트럭'
    AND DURATION_TYPE LIKE '7일 이상'
);

SET @THIRTY = (
    SELECT (100 - DISCOUNT_RATE) / 100
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN 
    WHERE CAR_TYPE LIKE '트럭'
    AND DURATION_TYPE LIKE '30일 이상'
);

SET @NINETY = (
    SELECT (100 - DISCOUNT_RATE) / 100
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN 
    WHERE CAR_TYPE LIKE '트럭'
    AND DURATION_TYPE LIKE '90일 이상'
);


WITH CAR AS (
    SELECT B.HISTORY_ID, A.CAR_ID, A.DAILY_FEE * (DATEDIFF(END_DATE, START_DATE) + 1) AS TOTAL_FEE, 
    DATEDIFF(END_DATE, START_DATE) + 1 AS DATE_DIFF
    FROM CAR_RENTAL_COMPANY_CAR A
    JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY B
    ON A.CAR_ID = B.CAR_ID
    WHERE A.CAR_TYPE LIKE '트럭'
)

SELECT DISTINCT HISTORY_ID, 
    CASE WHEN DATE_DIFF BETWEEN 7 AND 29 THEN ROUND(TOTAL_FEE * @SEVEN, 0)
         WHEN DATE_DIFF BETWEEN 30 AND 89 THEN ROUND(TOTAL_FEE * @THIRTY, 0)
         WHEN DATE_DIFF >= 90 THEN ROUND(TOTAL_FEE * @NINETY, 0)
         ELSE TOTAL_FEE
    END AS FEE
FROM CAR
ORDER BY 2 DESC, 1 DESC
;