자동차 대여 기록 별 대여 금액 구하기
https://school.programmers.co.kr/learn/courses/30/lessons/151141
문제
CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬해주세요.
예시
풀이
WITH test AS (
SELECT car.DAILY_FEE, car.CAR_TYPE, his.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 his
INNER JOIN CAR_RENTAL_COMPANY_CAR car ON car.CAR_ID = his.CAR_ID
WHERE car.CAR_TYPE = '트럭')
SELECT test.HISTORY_ID, ROUND(test.DAILY_FEE*test.period *(100 - IFNULL(plan.DISCOUNT_RATE, 0)) / 100) AS FEE
FROM test
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS plan
on plan.DURATION_TYPE = test.DURATION_TYPE
AND plan.CAR_TYPE = test.CAR_TYPE
ORDER BY 2 DESC, 1 DESC
- 처음으로 WITH절을 써봤다.
WTIH 절 (test 테이블)
1. HISTORY 테이블과 CAR 테이블을 CAR_ID로 INNER JOIN한다.
2. END_DATE와 START_DATE 차이를 계산하여 90일 이상인지, 30일 이상인지, 7일 이상인지, NONE인지 구분하여 DURATION_TYPE 컬럼으로 설정, 그리고 차이 계산한 것을 period 컬럼으로 설정
3. CAR_TYPE이 '트럭'인 것만 찾는다.
메인 쿼리
1. PLAN 테이블과 test 테이블을 DURATION_TYPE으로 LEFT JOIN한다.
2. plan.CAR_TYPE = test.CAR_TYPE으로 '트럭'인 것만 찾는다.
3. ROUND를 이용하여 (100-DISCOUNT_RATE) period DAILY_FEE / 100으로 FEE를 계산해준다.
배운 점
WITH 절
1개의 임시 테이블
WITH 임시테이블명 AS ( 서브쿼리 SELECT ~)
2개 이상의 임시 테이블
WITH 임시테이블명1 AS (서브쿼리),
임시 테이블명2 AS (서브쿼리)
- 이런 식으로 임시 테이블을 만들어서 메인 쿼리에서 재사용이 가능하다.
ORDER BY
- ORDER BY에서 매번 컬럼 이름을 써줬는데 숫자값으로 지정할 수도 있다.
=> 이때 숫자값은 select 절에 사용된 컬럼명들을 순서대로 1, 2, 3이다.
SELECT id, name, birth
FROM USER
ORDER BY 1, 3, 2 -- id, birth, name 순으로 오름차순 정렬