[SQL문제풀기] 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

문제

MYSQL

내가 작성한 정답

WITH A AS (SELECT CAR_ID, 
                    CASE WHEN START_DATE<= '2022-10-16'AND END_DATE>= '2022-10-16' THEN '대여중'
                    ELSE NULL END AS  REN
            FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY)
SELECT CAR_ID, IFNULL(MAX(REN),'대여 가능') AVAILABILITY
FROM A
GROUP BY CAR_ID
ORDER BY 1 DESC;

내가 이전에 작성한 정답

WITH A AS (
    SELECT CAR_ID,
        CASE
            WHEN START_DATE <= '2022-10-16' AND END_DATE >= '2022-10-16' THEN '대여중'
            ELSE '대여 가능'        
        END AS AVAILABILITY
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
)
SELECT CAR_ID, MAX(AVAILABILITY)
FROM A
GROUP BY CAR_ID
ORDER BY CAR_ID DESC

다른 사람들이 작성한 정답

SELECT CAR_ID
     , CASE
        WHEN MAX('2022-10-16' BETWEEN START_DATE AND END_DATE) = 1 THEN '대여중'
        ELSE '대여 가능'
        END AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC

ORACLE

내가 작성한 정답

#문자로 변경해서 비교
WITH A AS (SELECT CAR_ID,
							CASE WHEN TO_CHAR(START_DATE,'YYYY-MM-DD')<= '2022-10-16'AND TO_CHAR(END_DATE,'YYYY-MM-DD')>= '2022-10-16' THEN '대여중'
							ELSE NULL END AS  REN
							FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY)
SELECT CAR_ID, NVL(MAX(REN),'대여 가능') AVAILABILITY
FROM A
GROUP BY CAR_ID
ORDER BY 1 DESC;

#날짜로 변경해서 비교
WITH A AS (SELECT CAR_ID, 
                    CASE WHEN START_DATE<= DATE'2022-10-16'AND END_DATE>= DATE'2022-10-16' THEN '대여중'
                    ELSE NULL END AS  REN
            FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY)
SELECT CAR_ID, NVL(MAX(REN),'대여 가능') AVAILABILITY
FROM A
GROUP BY CAR_ID
ORDER BY 1 DESC;