Question

[ORACLE] LEVEL, CONNECT BY 와 OUTER JOIN을 이용한 오라클 쿼리공유합니다

-- 코드를 입력하세요
SELECT B.HH
, NVL(A.HH_CNT,0)

FROM
(
SELECT TOCHAR(DATETIME,'HH24') HH
, COUNT(TOCHAR(DATETIME,'HH24')) HHCNT
FROM ANIMALOUTS
WHERE TOCHAR(DATETIME,'HH24') BETWEEN '0' AND '23'
GROUP BY TOCHAR(DATETIME,'HH24')
ORDER BY TO_CHAR(DATETIME,'HH24')
) A,

    (
    SELECT LEVEL-1 HH  FROM DUAL CONNECT BY LEVEL  <= 24
    ) B

WHERE A.HH (+) = B.HH
ORDER BY HH

작성중인 코드―solution.sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 코드를 입력하세요
SELECT    B.HH 
        , NVL(A.HH_CNT,0) 

FROM
        (
        SELECT TO_CHAR(DATETIME,'HH24') HH
             , COUNT(TO_CHAR(DATETIME,'HH24')) HH_CNT
        FROM ANIMAL_OUTS 
        WHERE TO_CHAR(DATETIME,'HH24') BETWEEN '0' AND '23'
        GROUP BY TO_CHAR(DATETIME,'HH24')
        ORDER BY TO_CHAR(DATETIME,'HH24')
        ) A,

        (
        SELECT LEVEL-1 HH  FROM DUAL CONNECT BY LEVEL  <= 24
        ) B

WHERE A.HH (+) = B.HH
ORDER BY HH

230154

2020.9.24 13:52

[ORACLE] LEVEL, CONNECT BY 와 OUTER JOIN을 이용한 오라클 쿼리공유합니다

작성중인 코드―solution.sql

0 개의 답변

답변 쓰기

이 입력폼은 마크다운 문법을 지원합니다.