SELECT ANIMAL_TYPE, COUNT(*) AS COUNT
FROM ANIMAL_INS
WHERE ANIMAL_TYPE IN ('Cat', 'Dog') -- 고양이와 개
GROUP BY ANIMAL_TYPE -- 동물 종류로 그룹
ORDER BY ANIMAL_TYPE -- 순서: 고양이 > 개
SELECT NAME, COUNT(NAME)
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
GROUP BY NAME
HAVING COUNT(NAME) > 1
ORDER BY NAME
SELECT HOUR(DATETIME) AS HOUR, COUNT(*) AS COUNT
FROM ANIMAL_OUTS
-- WHERE HOUR(DATETIME) >= 9 AND HOUR(DATETIME) <= 19
WHERE 9 <= HOUR(DATETIME) <= 19
GROUP BY HOUR
ORDER BY HOUR
위의 (1)번 문제와 다르게, 테이블에 없는 시각까지 나타내야 한다.
따라서 WITH
를 이용하여 0시부터 23시 까지 값을 갖는 테이블을 만들고, 이후 JOIN
을 이용하여 입양 시각 별로 COUNT
로 센다.
WITH RECURSIVE HOUR_TABLE AS(
SELECT 0 AS HOUR
UNION ALL
SELECT HOUR + 1
FROM HOUR_TABLE
WHERE HOUR < 23
)
SELECT HOUR AS DATE, COUNT(ANIMAL_ID) AS COUNT
FROM HOUR_TABLE
LEFT OUTER JOIN ANIMAL_OUTS
ON HOUR(DATETIME) = HOUR_TABLE.HOUR
GROUP BY HOUR