데이터베이스 질문과 답변
by 김성진 [2006.11.02 14:26:01]
SELECT
CPCODE
, TO_CHAR(CRETIME, 'HH24') "CRETIME"
, NTFCODE "NTFCODE"
, COUNT(NTFCODE) "CNT"
FROM
LOG
WHERE
TO_CHAR(CRETIME, 'MM/DD') = TO_CHAR(SYSDATE-2, 'MM/DD')
GROUP BY
CPCODE, TO_CHAR(CRETIME, 'HH24'), NTFCODE;
CPCODE CR N CNT
------- -- - ----------
AP0010 10 t 2
AP0010 11 t 8
AP0010 11 v 1
AP0010 13 t 5
AP0010 14 t 6
AP0010 15 t 6
AP0010 16 t 1
AP0010 17 t 6
의 결과와
SELECT DISTINCT(NTFCODE) FROM LOG
N
-
O
Q
d
f
i
j
k
m
n
t
v
의 결과를 이용해서
CPCODE CR N CNT
------- -- - ----------
AP0010 00 O 0
AP0010 00 Q 0
AP0010 00 d 0
AP0010 00 f 0
AP0010 00 i 0
AP0010 00 j 0
AP0010 00 k 0
AP0010 00 m 0
AP0010 00 n 0
AP0010 00 t 0
AP0010 00 v 0
.
.
.
AP0010 10 O 0
AP0010 10 Q 0
AP0010 10 d 0
AP0010 10 f 0
AP0010 10 i 0
AP0010 10 j 0
AP0010 10 k 0
AP0010 10 m 0
AP0010 10 n 0
AP0010 10 t 2
AP0010 10 v 0
AP0010 11 O 0
AP0010 11 Q 0
AP0010 11 d 0
AP0010 11 f 0
AP0010 11 i 0
AP0010 11 j 0
AP0010 11 k 0
AP0010 11 m 0
AP0010 11 n 0
AP0010 11 t 8
AP0010 11 v 0
AP0010 12 O 0
AP0010 12 Q 0
AP0010 12 d 0
AP0010 12 f 0
AP0010 12 i 0
AP0010 12 j 0
AP0010 12 k 0
AP0010 12 m 0
AP0010 12 n 0
AP0010 12 t 0
AP0010 12 v 0
.
.
.
AP0010 23 O 0
AP0010 23 Q 0
AP0010 23 d 0
AP0010 23 f 0
AP0010 23 i 0
AP0010 23 j 0
AP0010 23 k 0
AP0010 23 m 0
AP0010 23 n 0
AP0010 23 t 0
AP0010 23 v 0
이렇게 만들여면 어떻게 해야 되나요 하루종일 쿼리가지고
이래저래 해보다가 안되서 부탁드립니다.
A.NTFCODE "NTFCODE"
, NVL(CNT, 0)
FROM
(SELECT /*+ RULE */
DISTINCT(NTFCODE)
FROM LOG
) A,
(SELECT
NTFCODE "NTFCODE"
, COUNT(NTFCODE) "CNT"
FROM
LOG
WHERE
TO_CHAR(CRETIME, 'MM/DD') = TO_CHAR(SYSDATE-2, 'MM/DD')
GROUP BY
NTFCODE
) B
WHERE
A.NTFCODE = B.NTFCODE(+)
이거는 되던데 왜 컴럼을 추가하면 안되지 ㅠㅠ
즉, 3개 컬럼으로 이루어진 기준정보를 만들어 이것과 3개 항목으로 아우터 조인을 해야 하죠.
, NVL(b.cnt,0) cnt
FROM
(
SELECT cpcode, cretime, ntfcode
FROM
(SELECT distinct cpcode FROM log),
(SELECT distinct ntfcode FROM log),
(SELECT LPAD(ROWNUM-1,2,'0') cretime FROM dual
CONNECT BY LEVEL <= 24)
) a,
(SELECT cpcode
, TO_CHAR(cretime, 'HH24') cretime
, ntfcode
, COUNT(ntfcode) cnt
FROM log
WHERE TO_CHAR(cretime, 'MM/DD') = TO_CHAR(SYSDATE-2, 'MM/DD')
GROUP BY cpcode, TO_CHAR(cretime, 'HH24'), ntfcode
) b
WHERE a.cpcode = b.cpcode(+)
AND a.cretime = b.cretime(+)
AND a.ntfcode = b.ntfcode(+)
*
ERROR at line 6:
ORA-01436: CONNECT BY loop in user data
>,< 이리저리 해보는데 잘 안되네요 마농님 에러좀 잡아주세효
CONNECT BY LEVEL <= 24
이부분을 바꿔보세요(위 쿼리는 9i이상)
SELECT LPAD(ROWNUM-1,2,'0') cretime FROM dict
WHERE ROWNUM <= 24
완성 >,<
SET LINESIZE 250
SET PAGESIZE 20000
COL CPCODE FORMAT A10
COL HH24 FORAMT 99
COL CNT FORMAT 999,999
COMPUTE SUM LABEL TOTAL OF "CNT" ON CPCODE
BREAK ON CPCODE SKIP 1 ON CRETIME
SELECT
A.CPCODE, A.CRETIME "HH24", A.NTFCODE, NVL(B.CNT,0) CNT
FROM
(
SELECT CPCODE, CRETIME, NTFCODE
FROM
(SELECT DISTINCT CPCODE FROM LOG),
(SELECT DISTINCT NTFCODE FROM LOG),
(SELECT LPAD(ROWNUM-1,2,'0') CRETIME FROM DICT WHERE ROWNUM <= 24 ) -- For 8i
-- (SELECT LPAD(ROWNUM-1,2,'0') CRETIME FROM DUAL CONNECT BY LEVEL <= 24) For 9i
) A,
(
SELECT
CPCODE, TO_CHAR(CRETIME, 'HH24') CRETIME, NTFCODE, COUNT(NTFCODE) CNT
FROM
LOG
WHERE
TO_CHAR(CRETIME, 'MM/DD') = TO_CHAR(SYSDATE-2, 'MM/DD')
GROUP BY
CPCODE, TO_CHAR(CRETIME, 'HH24'), NTFCODE
) B
WHERE
A.CPCODE = B.CPCODE(+)
AND A.CRETIME = B.CRETIME(+)
AND A.NTFCODE = B.NTFCODE(+)
컬럼으로 표시해볼라고 하는중인데.. 해보고
안되면 부탁좀 드릴께요. 감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.