07. 프로그래머스 [SQL] - 오랜 기간 보호한 동물(2)

OS	Windows 10 PRO 64bit 버전 20H2 (OS 빌드 19042.1165)
언어

링크

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

코딩테스트 연습 - 오랜 기간 보호한 동물(2)

 

정답1 (other person)

SELECT
    OUTS.ANIMAL_ID, OUTS.NAME
FROM
    ANIMAL_OUTS AS OUTS
RIGHT JOIN
    ANIMAL_INS AS INS
ON 
    INS.ANIMAL_ID = OUTS.ANIMAL_ID
ORDER BY
    DATEDIFF(OUTS.DATETIME, INS.DATETIME) DESC
LIMIT
    2;

 

정답2 (other person)

SELECT ANIMAL_ID,
       ANIMAL_INS.NAME
FROM ANIMAL_INS
    INNER JOIN ANIMAL_OUTS
        USING (ANIMAL_ID)
ORDER BY DATEDIFF(ANIMAL_OUTS.DATETIME,ANIMAL_INS.DATETIME) DESC
LIMIT 2

 

정답3 (other person)

select o.animal_id, o.name
from animal_outs o 
    inner join animal_ins i using(animal_id)
order by timestampdiff(minute, i.datetime, o.datetime) desc
limit 2;

 

정답4 (me)

SELECT ao.animal_id, ao.name
FROM animal_outs ao left join animal_ins ai
USING(animal_id)
ORDER BY ao.datetime - ai.datetime desc
LIMIT 2