입양 시각 구하기(2)

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

코딩테스트 연습 - 입양 시각 구하기(2)

MySQL

정답은 아닌 것은 알고 있지만...

SELECT HOUR(DATETIME) AS HOUR, COUNT(*) AS COUNT
FROM ANIMAL_OUTS 
GROUP BY HOUR
ORDER BY HOUR

재귀를 이용하자. 

WITH RECURSIVE TEMP AS (
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR+1 FROM TEMP WHERE HOUR<23
)

SELECT HOUR, count(ANIMAL_ID) AS COUNT
FROM TEMP LEFT JOIN ANIMAL_OUTS
    ON HOUR = HOUR(DATETIME)
GROUP BY HOUR
ORDER BY HOUR

Oracle

정답은 아니지만... 

SELECT TO_CHAR(DATETIME,'HH24') AS HOUR, COUNT(*) AS COUNT
FROM ANIMAL_OUTS 
GROUP BY TO_CHAR(DATETIME,'HH24')
ORDER BY TO_CHAR(DATETIME,'HH24')

재귀, 오라클의 재귀는 컬럼명 지정이 필수. 

WITH TEMP (HOUR) AS ( 
    SELECT 0 AS HOUR FROM DUAL
    UNION ALL 
    SELECT HOUR+1 FROM TEMP WHERE HOUR<23 ) 

SELECT HOUR, count(ANIMAL_ID) AS COUNT 
FROM TEMP LEFT JOIN ANIMAL_OUTS ON HOUR = TO_CHAR(DATETIME,'HH24') 
GROUP BY HOUR 
ORDER BY HOUR