[프로그래머스] 오랜 기간 보호한 동물 (1), (2)

(1)

-- ROWNUMBER 사용
SELECT NAME, DATETIME
    FROM (
        SELECT T1.NAME
            , T1.DATETIME
            , ROW_NUMBER() OVER(ORDER BY T1.DATETIME ASC) AS RNK
            FROM ANIMAL_INS T1
                LEFT JOIN ANIMAL_OUTS T2
                    ON T1.ANIMAL_ID = T2.ANIMAL_ID

            WHERE T2.ANIMAL_ID IS NULL
    ) T01
    WHERE RNK <= 3

-- LIMIT 사용
SELECT T1.NAME
    , T1.DATETIME
    FROM ANIMAL_INS T1
        LEFT JOIN ANIMAL_OUTS T2
            ON T1.ANIMAL_ID = T2.ANIMAL_ID
            
    WHERE T2.ANIMAL_ID IS NULL
    ORDER BY DATETIME
    LIMIT 3

 

(2)

SELECT ANIMAL_ID, NAME
    FROM (
        SELECT T1.*
            , ROW_NUMBER() OVER (ORDER BY T2.DATETIME - T1.DATETIME DESC) AS RNK
            FROM ANIMAL_INS T1
                INNER JOIN ANIMAL_OUTS T2
                    ON T1.ANIMAL_ID = T2.ANIMAL_ID
    ) T01
    
    WHERE RNK <= 2 
    ORDER BY RNK