My Dev Blog

<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 HOUR
     , COUNT(B.DATETIME) AS COUNT
  FROM (
         SELECT LEVEL-1 AS HOUR
           FROM DUAL
        CONNECT BY LEVEL <= 24
       ) A
LEFT OUTER JOIN ANIMAL_OUTS B
ON A.HOUR = TO_CHAR(B.DATETIME, 'HH24')
GROUP BY HOUR
ORDER BY HOUR

프로그래머스 입양시각구하기 SQL

<MySQL>

SELECT A.HOUR
     , A.COUNT
  FROM (
         SELECT DATE_FORMAT(DATETIME, '%Y-%m-%d %H:%i:%s') AS TOTAL_DATE
              , DATE_FORMAT(DATETIME, '%H') AS HOUR
              , COUNT(SUBSTR(DATETIME, 12, 2)) AS COUNT
           FROM ANIMAL_OUTS
       GROUP BY SUBSTR(DATETIME, 12, 2)
       ) A
 WHERE A.HOUR BETWEEN 9 AND 20
ORDER BY HOUR

<Oracle>

SELECT A.HOUR
     , A.COUNT
  FROM (
         SELECT TO_CHAR(DATETIME, 'HH24') AS HOUR
              , COUNT(TO_CHAR(DATETIME, 'HH24')) AS COUNT
           FROM ANIMAL_OUTS
       GROUP BY TO_CHAR(DATETIME, 'HH24')
       ) A
 WHERE A.HOUR BETWEEN 9 AND 20
ORDER BY HOUR

프로그래머스 입양시각구하기 SQL