[GROUP BY/LEVEL4] 입양 시각 구하기(2) (MySQL/Oracle)

MySQL

SET @HOUR = -1;
SELECT  (@HOUR := @HOUR +1) AS HOUR
      , (SELECT COUNT(HOUR(DATETIME)) 
        FROM ANIMAL_OUTS 
        WHERE HOUR(DATETIME)=@HOUR) AS COUNT 
FROM ANIMAL_OUTS
WHERE @HOUR < 23;

 

---

 

Oracle

SELECT D.HOUR, NVL(O.CNT,0)AS COUNT 
FROM (SELECT LEVEL - 1 AS HOUR FROM DUAL CONNECT BY LEVEL <= 24) D,
    (SELECT TO_CHAR(DATETIME,'HH24') AS HOUR, COUNT(*) AS CNT FROM ANIMAL_OUTS
    GROUP BY TO_CHAR(DATETIME,'HH24'))O
WHERE O.HOUR(+) = D.HOUR
ORDER BY HOUR