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

• WITH VALUE AS ( ... )
  • 테이블을 만들고, 후에 SELECT FROM VALUE를 통해 이용

WITH VALUE AS (
    SELECT A.daily_fee, A.car_type, B.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_car AS A
JOIN car_rental_company_rental_history AS B ON A.car_id = B.car_id
WHERE A.car_type = '트럭')   
-- value절
SELECT VALUE.history_id, 
    ROUND(VALUE.daily_fee * VALUE.period * 
          (100 - IFNULL(plan.discount_rate,0)) / 100) AS FEE
FROM VALUE
LEFT JOIN car_rental_company_discount_plan AS plan 
    ON plan.duration_type = VALUE.duration_type 
    AND plan.car_type = VALUE.car_type
ORDER BY 2 DESC, 1 DESC