[SQL] (자동차 대여 기록 별 대여 금액 구하기) + 특정문자열 지우고 저장하기

문제

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬해주세요

내 풀이

with truck as (
select car_id, car_type, daily_fee
from car_rental_company_car
where car_type = '트럭'),
datefee as (
select h.history_id, t.car_type, t.daily_fee
    ,datediff(h.end_date,h.start_date)+1 as date
from truck t
join car_rental_company_rental_history h on t.car_id = h.car_id)
, total as (
select history_id, daily_fee, date,
    case when date < 7 then 0
         when date < 30 then 5
         when date < 90 then 7 
         else 10
         end as discount_rate 
from datefee)
select history_id, 
       round((date*((100-ifnull(discount_rate,0))/100)*daily_fee),0) as fee
from total 
order by fee desc, history_id desc

이.. 이렇게 열심히 풀었는데 틀렸다. 왜냐 면 .. 예시로 준 퍼센트랑.. 테스트케이스 퍼센트가.. 달랐기 때문에 낚였다. 웩

->

바꾸니까 됨. 웩

아무튼 이렇게 지정해주지 않아도 된다.

다른 사람 풀이

select n.history_id, round(((datediff(n.end_date, n.start_date)+1) * n.daily_fee) * ((100- ifnull(d.discount_rate,0))/100)) fee from
( select h.history_id, c.car_type, c.daily_fee, h.start_date, h.end_date
        , case when datediff(h.end_date, h.start_date)+1 >= 90 then '90일 이상'
            when datediff(h.end_date, h.start_date)+1 >= 30 then '30일 이상'
            when datediff(h.end_date, h.start_date)+1 >= 7 then '7일 이상'
            else 0 end as duration 
        from car_rental_company_rental_history h, car_rental_company_car c
        where h.car_id = c.car_id
        and c.car_type = '트럭' ) n
left join (select duration_type, discount_rate
            from car_rental_company_discount_plan
            where car_type = '트럭') d
    on n.duration = d.duration_type
    order by fee desc, n.history_id desc;

근데 ㅋㅋ 다른 사람 풀이 봐두 잘 안들어온다..
내가 짠게 아니니까....

여담)
너무 똑똑한 짓피티.

이렇게 말햇더니

WITH truck AS (
    SELECT 
        car_id,
        car_type,
        daily_fee
    FROM car_rental_company_car
    WHERE car_type = '트럭'
),
datefee AS (
    SELECT
        h.history_id,
        t.car_type,
        t.daily_fee,
        DATEDIFF(h.end_date, h.start_date) + 1 AS date
    FROM truck t
    JOIN car_rental_company_rental_history h
      ON t.car_id = h.car_id
),
total AS (
    SELECT
        d.history_id,
        d.daily_fee,
        d.date,
        ( SELECT
          FROM car_rental_company_discount_plan p
          WHERE p.car_type = d.car_type
              -- 날짜가 기준일수 이상인 레코드 모두 매칭
              AND d.date >= CAST(SUBSTRING_INDEX(p.duration_type, '일 이상', 1) AS UNSIGNED)
            -- 그중에서 가장 큰 기준일수(예: 90)가 우선되도록 내림차순 정렬
            ORDER BY CAST(SUBSTRING_INDEX(p.duration_type, '일 이상', 1) AS UNSIGNED) DESC
            LIMIT 1
        ) AS discount_rate
    FROM datefee d
)
SELECT
    history_id,
    ROUND(
        date * (
            (100 - IFNULL(discount_rate, 0)) / 100
        ) * daily_fee,
        0
    ) AS fee
FROM total
ORDER BY fee DESC, history_id DESC;

이렇게 짜줌. ..대박!!

SELECT
          FROM car_rental_company_discount_plan p
          WHERE p.car_type = d.car_type
              -- 날짜가 기준일수 이상인 레코드 모두 매칭
              AND d.date >= CAST(SUBSTRING_INDEX(p.duration_type, '일 이상', 1) AS UNSIGNED)
            -- 그중에서 가장 큰 기준일수(예: 90)가 우선되도록 내림차순 정렬
            ORDER BY CAST(SUBSTRING_INDEX(p.duration_type, '일 이상', 1) AS UNSIGNED) DESC
            LIMIT 1

추가로 뒤에 문자열을 지우고 저장하는 방법을 없을까? 생각해보았다. %아닌 다른 문자열이 들어갈 수도 있으니까.

지우고 저장 = update set!

1) 숫자(0-9)와 소수점(.)이 아닌 모든 문자를 제거

UPDATE your_table
SET discount = REGEXP_REPLACE(discount, '[^0-9.]', '');

2) %와 퍼센트를 제거

UPDATE your_table
SET discount = REPLACE(REPLACE(discount, '%', ''), '퍼센트', '');

3) 뒤에 항상 1글자를 제거

UPDATE your_table
SET discount = SUBSTRING(discount, 1, LENGTH(discount) - 1);