Question

[Mysql] 정답 코드 공유합니다. 조금 코드가 더럽습니다...

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

할인율 계산시 직접 숫자를 입력하는 것이 아닌, 컬럼명으로 만들어서 계산하고 싶어서 위 코드처럼 작성했는데 조금 코드가 복잡하고 보기 안좋은 느낌이 있네요...!! 혹시 특이사항 있으시다면 지적 부탁드립니다 :)

작성중인 코드―solution.sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
WITH sub1 AS (SELECT car_type
                     , duration_type
                     , CASE WHEN discount_rate LIKE "5%" THEN 0.95
                            WHEN discount_rate LIKE "8%" THEN 0.92
                            ELSE 0.85
                       END AS dis1
                FROM car_rental_company_discount_plan
                WHERE car_type = "트럭"

), sub2 AS (SELECT a2.history_id
                 , a1.daily_fee
                 , a2.start_date
                 , a2.end_date
                 , a1.car_type
                 , DATEDIFF(a2.end_date, a2.start_date) + 1 AS day
                 , CASE WHEN DATEDIFF(a2.end_date, a2.start_date) + 1 >= 90 THEN "90일 이상"
                        WHEN DATEDIFF(a2.end_date, a2.start_date) + 1 >= 30 THEN "30일 이상"
                        WHEN DATEDIFF(a2.end_date, a2.start_date) + 1 >= 7 THEN "7일 이상"
                        ELSE "7일 미만"
                   END AS dis2
            FROM car_rental_company_car AS a1
                 INNER JOIN car_rental_company_rental_history AS a2 ON a1.car_id = a2.car_id
            WHERE a1.car_type = "트럭"
)

SELECT sub2.history_id AS HISTORY_ID
     , CASE WHEN sub1.dis1 IS NULL THEN ROUND(sub2.daily_fee * sub2.day)
            ELSE ROUND(sub2.daily_fee * sub2.day * sub1.dis1)
       END AS FEE 
FROM sub2
     LEFT JOIN sub1 ON sub1.car_type = sub2.car_type
                    AND sub1.duration_type = sub2.dis2
ORDER BY FEE DESC, HISTORY_ID DESC

안영래

2023.1.16 20:12

[Mysql] 정답 코드 공유합니다. 조금 코드가 더럽습니다...

작성중인 코드―solution.sql

0 개의 답변

답변 쓰기

이 입력폼은 마크다운 문법을 지원합니다. 마크다운 가이드 를 참고하세요.