SELECT REPLACE(LPAD(NVL(DT_HOURS, b.lv),2, '0'), '24', '00') AS DT_HOURS,
NVL ( PV_COUNT, 0 ) AS PV_COUNT
FROM (
SELECT COUNT(*) AS PV_COUNT
, TO_CHAR ( a.action_time, 'HH24' ) AS DT_HOURS
FROM ci_visitor_actions a
WHERE a.action_time BETWEEN TO_DATE ( '2014/01/20' , 'YYYY/MM/DD' )
AND TO_DATE ( '2014/01/20' , 'YYYY/MM/DD' ) + 0.99999
AND a.id_site = 1
GROUP BY TO_CHAR ( a.action_time, 'HH24' )
union all
SELECT COUNT(*) AS PV_COUNT2
, TO_CHAR ( a.action_time, 'HH24' ) AS DT_HOURS2
FROM ci_visitor_actions a
WHERE a.action_time BETWEEN TO_DATE ( '2013/12/22' , 'YYYY/MM/DD' )
AND TO_DATE ( '2014/01/21' , 'YYYY/MM/DD' ) + 0.99999
AND a.id_site = 1
GROUP BY TO_CHAR ( a.action_time, 'HH24' )
) a , ( SELECT LEVEL lv FROM DUAL CONNECT BY 24 >= LEVEL ) b
WHERE a.DT_HOURS (+) = b.lv
ORDER BY DT_HOURS
이 sql문을 실행하면
DT_HOURS | PV_COUNT
00 0
01 0
02 0
03 0
04 0
05 0
06 0
07 0
08 0
09 5
09 5
10 16
11 10
12 0
13 6
14 0
15 4
15 4
16 15
16 15
17 2
17 2
18 0
19 0
20 0
21 0
22 0
23 0
결과가 나옵니다.
제가 원하는 값은 union all로 묶여진 두번째 select는 컬럼하나를 더 생성해서
DT_HOURS | PV_COUNT | PV_COUNT2
00 0 0
01 0 0
02 0 0
03 0 0
04 0 0
05 0 0
06 0 0
07 0 0
08 0 0
09 5 5
10 0 16
11 0 10
12 0 0
13 0 6
14 0 0
15 4 4
16 15 15
17 2 2
18 0 0
19 0 0
20 0 0
21 0 0
22 0 0
23 0 0
이런 결과를 원합니다.
정리하자면 union all 로 묶여진 쿼리 두가지를 컬럼을 다르게 출력하고 싶다는 말입니다.
지금은 PV_COUNT 하나의 컬럼에 모두 나오네요...
도와주세요 PV_COUNT2 컬럼으로 출력하는 방법이요~~~