-- ANIMAL_INS 의 DATETIME : 보호 시작일
-- ANIMAL_OUTS의 DATETIME : 입양일
-- 보호 시작일 보다 입양일이 더 빠른
-- 보호 시작일 순
SELECT A.ANIMAL_ID , A.NAME
FROM ANIMAL_OUTS A LEFT JOIN ANIMAL_INS B
ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE A.DATETIME < B.DATETIME -- 입양일 더 빠르면 값 낮은 것
ORDER BY B.DATETIME ASC;
SELECT A.ANIMAL_ID , A.NAME
FROM ANIMAL_OUTS A LEFT OUTER JOIN ANIMAL_INS B
ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE B.ANIMAL_ID IS NULL
ORDER BY A.ANIMAL_ID ASC;
LEFT JOIN 으로 써도 됨.
LEFT OUTER JOIN하고 같지만 , LEFT JOIN은 ON으로 조인 조건 명시 OUTER는 USING으로 같은 키 값을 적어준다.
MINUS를 이용해서 풀 수 있음
SELECT ANIMAL_ID,NAME
FROM ANIMAL_OUTS
MINUS
SELECT ANIMAL_ID,NAME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;
SELECT ID,NAME,HOST_ID
FROM PLACES
WHERE HOST_ID IN (SELECT HOST_ID FROM PLACES GROUP BY HOST_ID HAVING COUNT(ID) >= 2)
ORDER BY ID ASC;
------- X ------
SELECT ID,NAME,HOST_ID
FROM PLACES
WHERE HOST_ID = (SELECT HOST_ID FROM PLACES GROUP BY HOST_ID HAVING COUNT(ID) >= 2)
ORDER BY ID ASC;
=을 사용하면 하나의 데이터만 가져오기 때문에 IN을 써야함 =을 쓰려면 DISTINCT로 한 개 데이터 가져올때..
처음에 COUNT(HOST_ID)로 조건을 줬는데 HOST_ID로 그룹을 했기 때문에 말이 안됨..
Review🤩
MY SQL은 SELECT FROM 절에 AS가 가능하다!
2022.07.08 추가
SELECT ID,NAME,HOST_ID
FROM PLACES
WHERE EXISTS ( SELECT HOST_ID
FROM PLACES
GROUP BY HOST_ID
HAVING COUNT(ID) >= 2
)
ORDER BY ID;
EXISTS를 이용해서 IN 대신 사용해봤는데 결과 값이 달랐다.
EXISTS : 조건에 해당하는 ROW의 존재 유무와 체크 후 더이상 수행하지 않음.
IN : 조건에 해당하는 ROW의 컬럼을 비교하여 체크한다.
IN을 이용해서 가공된 값을 볼 수 있지만 EXISTS는 IF문과 같이 있으면 수행시키고 , 가공되지는 않고 없으면 쿼리가 실행이 되지 않는다.
SELECT ID,NAME,HOST_ID
FROM PLACES a
WHERE EXISTS ( SELECT 1 -- 이 서브쿼리에서 다른 컬럼들은 불필요
FROM PLACES b
where a.host_id = b.host_id
GROUP BY HOST_ID
HAVING COUNT(host_ID) >= 2
)
ORDER BY ID;
아직 입양을 못간 동물 중 가장 오래 보호소에 있는 동물 3마리, 보호 시작일 순으로 조회
두번째는
DATE TIME이 필요하고 , 입양을 갔기 때문에 OUT 테이블에 기록이 있어야 함
LIMIT을 사용해도 되고, ROWNUM을 이용해도 됨.
Solution✍
SELECT NAME , DATETIME
FROM ( SELECT A.NAME
, A.DATETIME
FROM ANIMAL_INS A LEFT JOIN ANIMAL_OUTS B on A.ANIMAL_ID = B.ANIMAL_ID
WHERE B.ANIMAL_ID IS NULL -- LEFT JOIN이므로 안맞으면 비어 있음 OUT한 기록 X
ORDER BY A.DATETIME
)
WHERE ROWNUM <= 3;
SELECT NAME , DATETIME
FROM (
SELECT A.NAME
, A.DATETIME
, DENSE_RANK() OVER(ORDER BY A.DATETIME ASC) AS rnk
FROM ANIMAL_INS A LEFT JOIN ANIMAL_OUTS B
ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE B.ANIMAL_ID IS NULL
ORDER BY DATETIME ASC
)
WHERE rnk <= 3;
SELECT ANIMAL_ID , NAME
FROM ( SELECT A.ANIMAL_ID
, (TO_DATE(B.DATETIME) - TO_DATE(A.DATETIME)) AS DIFFDATETIME
, A.NAME
FROM ANIMAL_INS A LEFT JOIN ANIMAL_OUTS B on A.ANIMAL_ID = B.ANIMAL_ID
WHERE B.ANIMAL_ID IS NOT NULL -- 입양을 갔기 떄문에 NOT NULL B 테이블에 입양온 기록이 있어야 함.
ORDER BY DIFFDATETIME DESC
)
WHERE ROWNUM <= 2;
-- HAVING 하고 WHERE의 차이점
-- HAVING은 GROUP BY 하고난 뒤 , WHERE 그 이전 전체 데이터 검색
-- 코드를 입력하세요
SELECT NAME,COUNT(NAME) AS COUNT
FROM ANIMAL_INS
GROUP BY NAME
HAVING COUNT > 1
AND NAME IS NOT NULL
ORDER BY NAME;
-- TO_CHAR 이용해서 BETWEEN
-- GROUP BY 할 때, AS조건으로 넣으면 안들어가짐 , 조건 같이 넣어야함
-- HAVING절을 이용해서 가공된 시간 BETWEEN 걸어서 사이에 넣기
SELECT TO_CHAR(DATETIME,'HH24') AS HOUR, COUNT(DATETIME) AS COUNT
FROM ANIMAL_OUTS
GROUP BY TO_CHAR(DATETIME,'HH24')
HAVING TO_CHAR(DATETIME,'HH24') BETWEEN '09' AND '20'
ORDER BY HOUR;
-- 정답 [ X ]
-- 마지막 문제 쿼리는 LEVEL 쿼리로 만들어주거나 조인 사용해야 함
SELECT TO_CHAR(DATETIME,'HH24') AS HOUR ,
COUNT(DATETIME) AS COUNT
FROM ANIMAL_OUTS
GROUP BY TO_CHAR(DATETIME,'HH24')
HAVING TO_CHAR(DATETIME,'HH24') BETWEEN '00' AND '23'
ORDER BY HOUR;
-- 서브쿼리 임시테이블 만듬
-- tmp 임시테이블을 만드는데 레벨 0부터 23까지 줌
with tmp as (
select level-1 as hour
from dual
connect by level < 25
)
select * from tmp;
위의 쿼리로 조회를 하면 0~23까지 나옴
-- 정답 [ O ]
with tmp as (
select level-1 as hour
from dual
connect by level < 25
)
select a.hour
, nvl(b.count, 0)
from tmp a
left outer join
(
select to_char(datetime, 'HH24') as hour
, count(*) as count
from animal_outs
group by to_char(datetime, 'HH24')
) b
on a.hour = b.hour
order by a.hour
;
-- SELECT * FROM animal_outs WHERE to_char(datetime, 'HH24') = '07';
-- 조회가 가능하므로 조인절 해당 조건으로 준다.
그 다음 nvl이용해서 count없으면 0으로 중복이 있으면 안되므로 tmp에 left outer join을 걸어줌