-
입양 시각 구하기(2)SQL/SQL 연습문제 풀이 2022. 1. 19. 17:24반응형
https://programmers.co.kr/learn/courses/30/lessons/59413
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
반응형