[SQL문제풀기] 입양 시각 구하기(2)

silver

Jun 19, 2025

Contents

문제 MYSQL ORACLE

문제

school.programmers.co.kr

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

MYSQL

내가 작성한 정답


WITH RECURSIVE TIME AS (
    SELECT 0 HOUR
    UNION
    SELECT HOUR+1
    FROM TIME
    WHERE HOUR < 23
)
SELECT T.HOUR, COUNT(ANIMAL_ID) COUNT
FROM ANIMAL_OUTS A
RIGHT JOIN TIME T ON HOUR(A.DATETIME) = T.HOUR
GROUP BY T.HOUR
ORDER BY 1;

💡

WITH RECURSIVE cte_name AS ( -- 1) Anchor member (초기 쿼리) : 항상 한 번 실행 SELECT ...

UNION ALL -- 또는 UNION

- 2) Recursive member (재귀 쿼리) : 결과를 재입력해 반복 수행 SELECT ... FROM cte_name JOIN ... WHERE termination_condition ) SELECT ... FROM cte_name;

ORACLE

내가 작성한 정답


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

ORACLE에서 계층 쿼리


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

  •	DUAL : Oracle의 더미 테이블 (한 줄만 존재)
	•	LEVEL : Oracle이 제공하는 가상 컬럼 (1부터 시작)
	•	CONNECT BY : 자기 자신과 재귀적으로 연결하는 구문