SELECT ANIMAL_OUTS.ANIMAL_ID , ANIMAL_OUTS.NAME
FROM ANIMAL_OUTS
LEFT JOIN ANIMAL_INS
ON ANIMAL_INS.ANIMAL_ID = ANIMAL_OUTS.ANIMAL_ID
WHERE ANIMAL_INS.ANIMAL_ID IS NULL
ORDER BY ANIMAL_ID;
나중에 다시 간단 정리하기
출처 : https://dsin.wordpress.com/2013/03/16/sql-join-cheat-sheet/
SELECT ANIMAL_INS.ANIMAL_ID, ANIMAL_INS.NAME
FROM ANIMAL_INS
LEFT JOIN ANIMAL_OUTS
ON ANIMAL_INS.ANIMAL_ID = ANIMAL_OUTS.ANIMAL_ID
WHERE ANIMAL_INS.DATETIME > ANIMAL_OUTS.DATETIME
ORDER BY ANIMAL_INS.DATETIME;
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 DATETIME
LIMIT 3;
SELECT ID,NAME,HOST_ID
FROM PLACES
WHERE HOST_ID IN(
SELECT HOST_ID
FROM PLACES
GROUP BY HOST_ID
HAVING COUNT(HOST_ID) >1);
중복된 HOST_ID 값을 찾아서(HOST_ID를 그룹으로 묶어서 HOST_ID묶음들을 각각 PLACES 테이블에서 HOST_ID개수가 1보다 많은것) 그 값의 ID,NAME,HOST_ID를 읽기
SELECT ANIMAL_OUTS.ANIMAL_ID, ANIMAL_OUTS.NAME
FROM ANIMAL_OUTS
LEFT JOIN ANIMAL_INS
ON ANIMAL_INS.ANIMAL_ID = ANIMAL_OUTS.ANIMAL_ID
ORDER BY TIMESTAMPDIFF(MINUTE,ANIMAL_INS.DATETIME, ANIMAL_OUTS.DATETIME) DESC
LIMIT 2;
남이한 거 보고 품. 난글렀어
SELECT DISTINCT CART_ID
FROM CART_PRODUCTS
WHERE NAME = 'Milk' AND CART_ID IN
(
SELECT DISTINCT CART_ID
FROM CART_PRODUCTS
WHERE NAME = 'Yogurt'
)
ORDER BY CART_ID
이 문제 정리잘된 곳 < 다시 정리 후 공부
Lv.4 에서 유일하게 푼 것 나머지 다시 정리 복습 ㅠㅠ
SELECT ANIMAL_OUTS.ANIMAL_ID, ANIMAL_OUTS.ANIMAL_TYPE,ANIMAL_OUTS.NAME
FROM ANIMAL_OUTS
LEFT JOIN ANIMAL_INS
ON ANIMAL_INS.ANIMAL_ID = ANIMAL_OUTS.ANIMAL_ID
WHERE ANIMAL_INS.SEX_UPON_INTAKE LIKE 'Intact %' AND
(ANIMAL_OUTS.SEX_UPON_OUTCOME LIKE 'Spayed %' OR
ANIMAL_OUTS.SEX_UPON_OUTCOME LIKE 'Neutered %')
ORDER BY ANIMAL_OUTS.ANIMAL_ID