[프로그래머스] 입양 시각 구하기(2)(SQL)

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

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

 

나의 답 :

select a.hour, nvl(b.count, 0) --값이 없을 수 있어서 nvl사용
from 
(
select rnum hour 
	from (
        select (rownum-1) rnum 
        from animal_outs --기존 테이블을 활용해서 0~100을 갖는 테이블 추출
) where rnum < 24) a --0~23 뽑기
left outer join -- 값이 안 들어있는 시간대도 사용하기 위해 left outer join
( 
    select to_char(datetime, 'hh24') as hour, count(*) as count
    from animal_outs group by to_char(datetime,'hh24')
) b
on a.hour = b.hour
order by a.hour

 

 

다른 사람의 답 : with ~ as , connect by 사용

with tmp as (
select level-1 as hour
from dual
connect by level < 25
)

select a.hour
, nvl(b.count, 0)
from tmp a
left outer join
(
select tochar(datetime, 'HH24') as hour
, count(*) as count
from animalouts
group by to_char(datetime, 'HH24')
) b
on a.hour = b.hour
order by a.hour
;