[MySQL] 프로그래머스 157339. 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/157339

프로그래머스

정답 코드

SELECT CAR.CAR_ID, CAR.CAR_TYPE, ROUND((CAR.DAILY_FEE * 30)*(1-DISCOUNT.DISCOUNT_RATE/100),0) AS FEE
FROM CAR_RENTAL_COMPANY_CAR AS CAR
JOIN (SELECT CAR_TYPE, DISCOUNT_RATE
      FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
      WHERE DURATION_TYPE = '30일 이상'
     ) AS DISCOUNT
ON CAR.CAR_TYPE = DISCOUNT.CAR_TYPE
WHERE CAR.CAR_TYPE IN ('세단', 'SUV')
AND CAR.CAR_ID NOT IN(SELECT DISTINCT CAR_ID
                     FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                     WHERE END_DATE > '2022-11-01')
AND (CAR.DAILY_FEE * 30)*(1-DISCOUNT.DISCOUNT_RATE/100)  >= 500000
AND (CAR.DAILY_FEE * 30)*(1-DISCOUNT.DISCOUNT_RATE/100)  < 2000000
ORDER BY (CAR.DAILY_FEE * 30)*(1-DISCOUNT.DISCOUNT_RATE/100) DESC,
CAR.CAR_TYPE, CAR.CAR_ID;

WITH CalculatedFees AS (
    SELECT 
        CAR.CAR_ID, 
        CAR.CAR_TYPE, 
        ROUND((CAR.DAILY_FEE * 30) * (1 - DISCOUNT.DISCOUNT_RATE / 100), 0) AS FEE
    FROM 
        CAR_RENTAL_COMPANY_CAR AS CAR
    JOIN 
        (SELECT CAR_TYPE, DISCOUNT_RATE
         FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
         WHERE DURATION_TYPE = '30일 이상') AS DISCOUNT
    ON 
        CAR.CAR_TYPE = DISCOUNT.CAR_TYPE
)

SELECT * 
FROM CalculatedFees
WHERE 
    CAR_TYPE IN ('세단', 'SUV')
    AND CAR_ID NOT IN (
        SELECT DISTINCT CAR_ID
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
        WHERE END_DATE > '2022-11-01'
    )
    AND FEE >= 500000
    AND FEE < 2000000
ORDER BY FEE DESC, CAR_TYPE, CAR_ID DESC;

# 다른 분 풀이 https://school.programmers.co.kr/questions/71950

# temp - [2022-11-01 ~ 2022-11-30]에 대여가능한 CAR_ID 추출
with temp as (
    select CAR_ID, max(END_DATE) as END_DATE
    from CAR_RENTAL_COMPANY_RENTAL_HISTORY 
    group by CAR_ID
    having year(max(END_DATE)) <= 2022 and month(max(END_DATE)) <= 10
),
# temp2 대여가능한 것 중에서 CAR_TYPE이 SUV와 세단인 CAR의 정보
temp2 as (
    select T.CAR_ID, C.CAR_TYPE, C.DAILY_FEE
    from temp T
    inner join CAR_RENTAL_COMPANY_CAR C on T.CAR_ID = C.CAR_ID
    where C.CAR_TYPE in ('SUV', '세단')
),
temp3 as (
    select T2.CAR_ID, T2.CAR_TYPE,
            round(T2.DAILY_FEE * 30 * (100 - P.DISCOUNT_RATE) / 100, 0) as FEE
    from temp2 T2
    inner join CAR_RENTAL_COMPANY_DISCOUNT_PLAN P on T2.CAR_TYPE = P.CAR_TYPE
    where  P.DURATION_TYPE = '30일 이상'
)


select CAR_ID, CAR_TYPE, FEE
from temp3
where FEE between 500000 and 1999999
order by FEE desc, CAR_TYPE asc, CAR_ID desc;

 

With 구문 사용법

WITH cte_name (column1, column2, ...) AS (
    -- 공통 표현식 쿼리
    SELECT column1, column2, ...
    FROM table_name
    WHERE conditions
)
-- 메인 쿼리
SELECT *
FROM cte_name
WHERE conditions;

/*
cte_name: 공통 표현식의 이름을 지정합니다.
(column1, column2, ...): 공통 표현식의 결과에 포함될 열을 선택적으로 지정합니다. 이 부분은 필수가 아닙니다.
AS: 공통 표현식을 정의하기 위한 키워드입니다.
SELECT column1, column2, ... FROM table_name WHERE conditions: 공통 표현식의 본문입니다. 여기서 원하는 쿼리를 작성하여 임시 결과 세트를 생성합니다.
메인 쿼리: 공통 표현식을 사용하여 결과를 가져올 메인 쿼리입니다.
*/