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

문제

MYSQL

내가 작성한 정답1

select car_id, 
        case when m = 1 then '대여중' 
            else '대여 가능' end AVAILABILITY
from (select car_id, 
            min(case 
                when start_date <= '2022-10-16' and end_date >= '2022-10-16' then 1
                else 2     end ) m
    from CAR_RENTAL_COMPANY_RENTAL_HISTORY
    group by car_id) mm
order by 1 desc

내가 작성한 정답2

select car_id,
       case 
           when max(case 
                      when start_date <= '2022-10-16' and end_date >= '2022-10-16' then 1 
                      else 0 
                    end) = 1 then '대여중'
           else '대여 가능' 
       end AVAILABILITY
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
group by car_id
order by car_id 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

내가 작성한 정답1

select car_id, case when m=1 then '대여중' else '대여 가능' end AVAILABILITY
from (select car_id,
                max(case when to_char(start_date,'yymmdd') <= '221016' 
                        and to_char(end_date,'yymmdd') >= '221016' then 1
                    else 0  end ) m
        from CAR_RENTAL_COMPANY_RENTAL_HISTORY
        group by car_id)
order by car_id desc

내가 작성한 정답2

select car_id,
        case 
        when max(case 
		             when '221016' between to_char(start_date,'yymmd') and to_char(end_date,'yymmdd') then 9
		             else 0 end) = 9 then '대여중'
        else '대여 가능' 
        end 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,'YYMMDD') <= '221016' AND TO_CHAR(END_DATE,'YYMMDD') >= '221016' 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