문제

ANIMAL_INS와 ANIMAL_OUTS 테이블을 사용하여, 동물 보호소에 가장 오래 있었던 동물의 ID, 이름, 보호 기간을 구하는 SQL문을 작성하세요. 단, 보호 기간은 ANIMAL_OUTS.DATETIME - ANIMAL_INS.DATETIME 으로 계산합니다.

서브쿼리 활용

SELECT ANIMAL_ID, NAME, DURATION
FROM (
    SELECT AI.ANIMAL_ID,
           AI.NAME,
           DATEDIFF(AO.DATETIME, AI.DATETIME) AS DURATION
    FROM ANIMAL_INS AS AI
    JOIN ANIMAL_OUTS AS AO 
      ON AI.ANIMAL_ID = AO.ANIMAL_ID
) AS T
ORDER BY DURATION DESC
LIMIT 1;

CTR 방식

WITH DurationTable AS (
    SELECT AI.ANIMAL_ID,
           AI.NAME,
           DATEDIFF(AO.DATETIME, AI.DATETIME) AS DURATION 
    FROM ANIMAL_INS AS AI 
    JOIN ANIMAL_OUTS AS AO
        ON AI.ANIMAL_ID = AO.ANIMAL_ID 
)
SELECT ANIMAL_ID, NAME, DURATION 
FROM DurationTable
ORDER BY DURATION DESC 
LIMIT 1 

윈도우 함수 활용

SELECT ANIMAL_ID, NAME, DURATION
FROM (
    SELECT AI.ANIMAL_ID,
           AI.NAME,
           DATEDIFF(AO.DATETIME, AI.DATETIME) AS DURATION,
           RANK() OVER (ORDER BY DATEDIFF(AO.DATETIME, AI.DATETIME) DESC) AS RNK
    FROM ANIMAL_INS AS AI
    JOIN ANIMAL_OUTS AS AO
      ON AI.ANIMAL_ID = AO.ANIMAL_ID
) AS T
WHERE RNK = 1;

JOIN과 정렬(ORDER BY) 활용

SELECT AI.ANIMAL_ID, 
       AI.NAME,
       DATEDIFF(AO.DATETIME, AI.DATETIME) AS DURATION 
FROM ANIMAL_INS AS AI
JOIN ANIMAL_OUTS AS AO 
  ON AI.ANIMAL_ID = AO.ANIMAL_ID
ORDER BY DURATION DESC 
LIMIT 1