[Oracle] 입양 시각 구하기(2)

 

프로그래머스

• 제목 : 입양 시각 구하기(2)
• 주소 : https://programmers.co.kr/learn/courses/30/lessons/59413?language=oracle 

코딩테스트 연습 - 입양 시각 구하기(2)

 

풀이

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