[oracle] GROUP BY - 입양 시각 구하기(2)

have a good time 2025. 3. 25. 17:17

정답

SELECT A.HOUR, COUNT(B.ANIMAL_ID) AS COUNT
FROM (
    SELECT LEVEL-1 AS HOUR
    FROM DUAL
    CONNECT BY LEVEL <= 24
) A
LEFT JOIN ANIMAL_OUTS B
ON A.HOUR = TO_CHAR(B.DATETIME, 'HH24')
GROUP BY A.HOUR
ORDER BY A.HOUR;

1. 아래의 쿼리 실행 시

SELECT LEVEL-1 AS HOUR
FROM DUAL
CONNECT BY LEVEL <= 24

결과

2. 날짜, 시간 형태