[Oracle] 오랜 기간 보호한 동물(2)

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

프로그래머스

정답

SELECT ANIMAL_ID,NAME
FROM
    (SELECT ROWNUM RN, T.*
        FROM
            (SELECT B.DATETIME- A.DATETIME protection_period ,A.NAME,A.ANIMAL_ID
            FROM ANIMAL_OUTS B INNER JOIN ANIMAL_INS A
            ON A.ANIMAL_ID=B.ANIMAL_ID
            ORDER BY B.DATETIME- A.DATETIME DESC) T
    ) B
WHERE B.RN <=2
ORDER BY protection_period DESC
;

풀이

Step1) 인라인서브쿼리

SELECT B.DATETIME- A.DATETIME protection_period ,A.NAME,A.ANIMAL_ID
FROM ANIMAL_OUTS B INNER JOIN ANIMAL_INS A
	ON A.ANIMAL_ID=B.ANIMAL_ID
ORDER BY B.DATETIME- A.DATETIME DESC

Step 2) ROWNUM 사용 

SELECT ROWNUM RN, T.*
        FROM
            (SELECT B.DATETIME- A.DATETIME protection_period ,A.NAME,A.ANIMAL_ID
            FROM ANIMAL_OUTS B INNER JOIN ANIMAL_INS A
            ON A.ANIMAL_ID=B.ANIMAL_ID
            ORDER BY B.DATETIME- A.DATETIME DESC) T

SELECT RN,ANIMAL_ID,NAME
FROM
    (SELECT ROWNUM RN, T.*
        FROM
            (SELECT B.DATETIME- A.DATETIME protection_period ,A.NAME,A.ANIMAL_ID
            FROM ANIMAL_OUTS B INNER JOIN ANIMAL_INS A
            ON A.ANIMAL_ID=B.ANIMAL_ID
            ORDER BY B.DATETIME- A.DATETIME DESC) T
    ) B

 

SELECT RN,ANIMAL_ID,NAME
FROM
    (SELECT ROWNUM RN, T.*
        FROM
            (SELECT B.DATETIME- A.DATETIME protection_period ,A.NAME,A.ANIMAL_ID
            FROM ANIMAL_OUTS B INNER JOIN ANIMAL_INS A
            ON A.ANIMAL_ID=B.ANIMAL_ID
            ORDER BY B.DATETIME- A.DATETIME DESC) T
    ) B
WHERE B.RN <=2
ORDER BY protection_period DESC