[mysql] 오랜 기간 보호한 동물(1), 오랜 기간 보호한 동물(2), 있었는데요 없었습니다, 없어진 기록 찾기, 조건별로 분류하여 주문상태 출력하기

문제 설명은 문제 아래의 링크를 참고.

오랜 기간 보호한 동물(1)

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

풀이

SELECT ANIMAL_INS.NAME, ANIMAL_INS.DATETIME
FROM ANIMAL_INS
LEFT JOIN ANIMAL_OUTS
ON ANIMAL_INS.ANIMAL_ID = ANIMAL_OUTS.ANIMAL_ID
WHERE ANIMAL_OUTS.ANIMAL_ID IS NULL
ORDER BY ANIMAL_INS.DATETIME
LIMIT 3;

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

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

풀이

SELECT ANIMAL_INS.ANIMAL_ID, ANIMAL_INS.NAME
FROM ANIMAL_INS 
JOIN ANIMAL_OUTS
ON ANIMAL_INS.ANIMAL_ID = ANIMAL_OUTS.ANIMAL_ID
WHERE ANIMAL_OUTS.ANIMAL_ID IS NOT NULL
ORDER BY DATEDIFF(ANIMAL_OUTS.DATETIME,ANIMAL_INS.DATETIME) DESC
LIMIT 2;

있었는데요 없었습니다

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

풀이

SELECT INS.ANIMAL_ID, INS.NAME 
FROM ANIMAL_INS AS INS
JOIN ANIMAL_OUTS AS OUTS
WHERE INS.ANIMAL_ID = OUTS.ANIMAL_ID AND OUTS.DATETIME <= INS.DATETIME 
ORDER BY INS.DATETIME;

없어진 기록 찾기

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

풀이

SELECT OUTS.ANIMAL_ID, OUTS.NAME
FROM ANIMAL_INS AS INS
RIGHT JOIN ANIMAL_OUTS AS OUTS
ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE INS.ANIMAL_ID IS NULL
ORDER BY OUTS.ANIMAL_ID

조건별로 분류하여 주문상태 출력하기

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

풀이

SELECT ORDER_ID, PRODUCT_ID, date_format(OUT_DATE, "%Y-%m-%d") AS OUT_DATE, 
    CASE
        WHEN OUT_DATE <= "2022-05-01"
        THEN "출고완료"
        WHEN OUT_DATE > "2022-05-01"
        THEN "출고대기"
        WHEN OUT_DATE IS NULL
        THEN "출고미정"
    END AS "출고여부"
FROM FOOD_ORDER
ORDER BY ORDER_ID;