#73. STRING, DATE : 자동차 대여 기록 별 대여 금액 구하기 | 프로그래머스 | SQL 고득점 Kit

문제

 

입력 코드

1. 

SELECT 
    RENT.HISTORY_ID, 
    ROUND(CAR.DAILY_FEE * (DATEDIFF(RENT.END_DATE, RENT.START_DATE)+1) 
          * (CASE WHEN DATEDIFF(END_DATE,START_DATE)+1 < 7 THEN 1
                  WHEN DATEDIFF(END_DATE,START_DATE)+1 < 30 THEN 0.95
                  WHEN DATEDIFF(END_DATE,START_DATE)+1 < 90 THEN 0.92
                  ELSE 0.85
             END)
         ) AS FEE
FROM CAR_RENTAL_COMPANY_CAR AS CAR 
    JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS RENT
    ON CAR.CAR_ID = RENT.CAR_ID
    JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS DISCOUNT
    ON CAR.CAR_TYPE = DISCOUNT.CAR_TYPE
WHERE CAR.CAR_TYPE = '트럭'
GROUP BY RENT.HISTORY_ID
ORDER BY FEE DESC, RENT.HISTORY_ID DESC;

 

2. WITH

WITH value AS (
    SELECT 
        CAR.DAILY_FEE, CAR.CAR_TYPE, RENT.HISTORY_ID,
        DATEDIFF(END_DATE, START_DATE) + 1 AS PERIOD,
    CASE 
      WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 90 THEN '90일 이상'
      WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 30 THEN '30일 이상'
      WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 7 THEN '7일 이상'
      ELSE 'NONE' END AS DURATION_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS RENT
    INNER JOIN CAR_RENTAL_COMPANY_CAR AS CAR 
    ON CAR.CAR_ID = RENT.CAR_ID
WHERE CAR.CAR_TYPE = '트럭')   


SELECT value.HISTORY_ID, 
    ROUND(value.DAILY_FEE * value.PERIOD * 
          (100 - IFNULL(DISCOUNT.DISCOUNT_RATE,0)) / 100) AS FEE
FROM value
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS DISCOUNT
    ON DISCOUNT.DURATION_TYPE = value.DURATION_TYPE 
    AND DISCOUNT.CAR_TYPE = value.CAR_TYPE
ORDER BY FEE DESC, HISTORY_ID DESC

 

WITH 절

• SQL에서 쿼리를 작성할 때 하나의 서브쿼리 또는 임시 테이블처럼 활용할 수 있는 기능
• [참고] WITH절을 효율적으로 사용하기

 

3. 

SELECT 
    HISTORY_ID,
    ROUND((DAILY_FEE * (100 - IFNULL(DISCOUNT_RATE, 0)) /100) * PERIOD) AS FEE
FROM 
(
    SELECT 
        CAR.CAR_ID, CAR.CAR_TYPE,
        DAILY_FEE, HISTORY_ID,
        TIMESTAMPDIFF(DAY, START_DATE, END_DATE) + 1 AS PERIOD,
    (CASE 
        WHEN TIMESTAMPDIFF(DAY, START_DATE, END_DATE) + 1 >= 90 THEN '90일 이상'
        WHEN TIMESTAMPDIFF(DAY, START_DATE, END_DATE) + 1 >= 30 THEN '30일 이상'
        WHEN TIMESTAMPDIFF(DAY, START_DATE, END_DATE) + 1 >= 7 THEN '7일 이상'
        ELSE '7일 미만' 
    END) AS DURATION_TYPE
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS HISTORY
        INNER JOIN CAR_RENTAL_COMPANY_CAR AS CAR
        ON CAR.CAR_ID = HISTORY.CAR_ID
) AS PERIOD_TABLE
    LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS PLAN
    ON PERIOD_TABLE.CAR_TYPE = PLAN.CAR_TYPE
AND PERIOD_TABLE.DURATION_TYPE = PLAN.DURATION_TYPE
WHERE PERIOD_TABLE.CAR_TYPE = '트럭'
ORDER BY FEE DESC, HISTORY_ID DESC

 

 

코드 설명

#SELECT #ROUND #DATEDIFF #CASE #JOIN #GROUP BY #ORDER BY #WITH

 

 

문제 출처

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