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

silver

Jun 06, 2025

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

Contents

문제 MYSQL ORACLE

문제

school.programmers.co.kr

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

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

💡

집계함수 MAX()안에 연산자를 넣으면 연산이 참이라면 1을 출력하고 거짓이라면 0을 출력

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;

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

#문자로 변경해서 비교 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;