WITH sub1 AS (SELECT cartype
, durationtype
, CASE WHEN discountrate LIKE "5%" THEN 0.95
WHEN discountrate LIKE "8%" THEN 0.92
ELSE 0.85
END AS dis1
FROM carrentalcompanydiscountplan
WHERE car_type = "트럭"
), sub2 AS (SELECT a2.historyid
, a1.dailyfee
, a2.startdate
, a2.enddate
, a1.cartype
, DATEDIFF(a2.enddate, a2.startdate) + 1 AS day
, CASE WHEN DATEDIFF(a2.enddate, a2.startdate) + 1 >= 90 THEN "90일 이상"
WHEN DATEDIFF(a2.enddate, a2.startdate) + 1 >= 30 THEN "30일 이상"
WHEN DATEDIFF(a2.enddate, a2.startdate) + 1 >= 7 THEN "7일 이상"
ELSE "7일 미만"
END AS dis2
FROM carrentalcompanycar AS a1
INNER JOIN carrentalcompanyrentalhistory AS a2 ON a1.carid = a2.carid
WHERE a1.car_type = "트럭"
)
SELECT sub2.historyid AS HISTORYID
, CASE WHEN sub1.dis1 IS NULL THEN ROUND(sub2.dailyfee * sub2.day)
ELSE ROUND(sub2.dailyfee * sub2.day * sub1.dis1)
END AS FEE
FROM sub2
LEFT JOIN sub1 ON sub1.cartype = sub2.cartype
AND sub1.durationtype = sub2.dis2
ORDER BY FEE DESC, HISTORYID DESC
할인율 계산시 직접 숫자를 입력하는 것이 아닌, 컬럼명으로 만들어서 계산하고 싶어서 위 코드처럼 작성했는데 조금 코드가 복잡하고 보기 안좋은 느낌이 있네요...!! 혹시 특이사항 있으시다면 지적 부탁드립니다 :)