Table表数据结构如下:姓名 时间 数量
AAA 2009-10-01 00:12:26 15
AAA 2009-10-01 00:22:26 10
SSS 2009-10-01 01:22:26 15
DDD 2009-10-01 03:12:26 15
VVV 2009-10-01 07:52:26 15
HHH 2009-10-01 10:43:26 15
张三 2009-10-01 15:20:26 15
我想按小时统计上面的数据,该时间段没有数据则显示0,领导要求最后结果如下:
时间 数量
2009-10-01 00 25
2009-10-01 01 15
2009-10-01 02 0
2009-10-01 03 15
2009-10-01 04 0
2009-10-01 05 0
2009-10-01 06 0
2009-10-01 07 15
...
============================================================
我的语句只能写成这样:
select to_char(时间,'yyyy-mm-dd HH24') count(*)
from Table
Group By to_char(时间,'yyyy-mm-dd HH24')
============================================================
请问怎么在预设的条件下将数据库中没有的数据输出为0?
如凌晨两点没有数据,要显示出来为0
做图表用的
AAA 2009-10-01 00:12:26 15
AAA 2009-10-01 00:22:26 10
SSS 2009-10-01 01:22:26 15
DDD 2009-10-01 03:12:26 15
VVV 2009-10-01 07:52:26 15
HHH 2009-10-01 10:43:26 15
张三 2009-10-01 15:20:26 15
我想按小时统计上面的数据,该时间段没有数据则显示0,领导要求最后结果如下:
时间 数量
2009-10-01 00 25
2009-10-01 01 15
2009-10-01 02 0
2009-10-01 03 15
2009-10-01 04 0
2009-10-01 05 0
2009-10-01 06 0
2009-10-01 07 15
...
============================================================
我的语句只能写成这样:
select to_char(时间,'yyyy-mm-dd HH24') count(*)
from Table
Group By to_char(时间,'yyyy-mm-dd HH24')
============================================================
请问怎么在预设的条件下将数据库中没有的数据输出为0?
如凌晨两点没有数据,要显示出来为0
做图表用的
FROM all_objects
WHERE ROWNUM <= 24
这是生成24小时的语句
SELECT d.days, b.hours
FROM
(SELECT LPAD (TO_CHAR (ROWNUM - 1), 2, '0') hours
FROM all_objects
WHERE ROWNUM <= 24) b,
(SELECT TO_DATE ('2009-11-02', 'yyyy-mm-dd') + ROWNUM - 2 days
FROM all_objects
WHERE ROWNUM <=
TO_DATE ('2009-11-02', 'yyyy-mm-dd')
- TO_DATE ('2009-11-02', 'yyyy-mm-dd')
+ 2) d这段语句是生成指定日期里每天24小时
SELECT t.days, t.hours, NVL (s.qty, 0) 数量
FROM (SELECT d.days, b.hours
FROM
(SELECT LPAD (TO_CHAR (ROWNUM - 1), 2, '0') hours
FROM all_objects
WHERE ROWNUM <= 24) b,
(SELECT TO_DATE ('2009-11-02', 'yyyy-mm-dd') + ROWNUM - 2 days
FROM all_objects
WHERE ROWNUM <=
TO_DATE ('2009-11-02', 'yyyy-mm-dd')
- TO_DATE ('2009-11-02', 'yyyy-mm-dd')
+ 2) d
) t,
(SELECT TRUNC (时间) days,
TO_CHAR (时间, 'hh24') hours, SUM (数量) qty
FROM table
where 时间 BETWEEN TO_DATE ('2009-11-02', 'yyyy-mm-dd') - 1
AND TO_DATE ('2009-11-02', 'yyyy-mm-dd')
+ 0.99999
GROUP BY TRUNC (a.etr_date), TO_CHAR (a.etr_date, 'hh24')) s
WHERE t.hours = s.hours(+)
AND t.days = s.days(+)
(
select '2009-11-02 '||LPAD (TO_CHAR (ROWNUM-1), 2, '0') as HOURS , 0 as QTY
from all_objects where rownum < 25
union all
select to_char(时间,'YYYY-MM-DD HH24'), 数量
from 表
where to_date('2009-11-02','YYYY-MM-DD') <= trunc(时间)
and to_date('2009-11-02','YYYY-MM-DD') + 1 > trunc(时间) ) group by HOURS order by HOURS
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL < 25;下面语句用来生成当天24小时的序列。
SELECT TRUNC (SYSDATE) + (LEVEL - 1) / 24
FROM DUAL
CONNECT BY LEVEL < 25;
(select trunc("时间", 'hh') d1, sum("数量") cnt from TABLE1 where trunc("时间", 'dd')=to_date('2009-10-1', 'yyyy-mm-dd') group by trunc("时间", 'hh')) b on to_number(to_char(b.d1, 'hh24'))=a.no1
(select trunc("时间", 'hh') d1, sum("数量") cnt from TABLE1 where trunc("时间", 'dd')=to_date('2009-10-1', 'yyyy-mm-dd') group by trunc("时间", 'hh')) b on to_number(to_char(b.d1, 'hh24'))=a.no1
今天在回复问题时也想到这个问题
应该说各有所长
用all_object表,对于产生数据多的话,性能会比用connect的性能好点
如果数据不多,并且帐号权限不能访问all_object的话,还是用connect比较方便点
WITH tt AS
(
SELECT '2009-11-01 00:12:26' times, 5 counts
FROM DUAL
UNION ALL
SELECT '2009-11-01 00:12:26', 15
FROM DUAL
UNION ALL
SELECT '2009-11-01 00:22:26', 10
FROM DUAL
UNION ALL
SELECT '2009-11-01 05:22:26', 15
FROM DUAL
UNION ALL
SELECT '2009-11-01 03:12:26', 15
FROM DUAL)
SELECT times_exit, SUM (counts)
FROM ((SELECT a.times_exit, SUM (a.counts) AS counts
FROM (SELECT SUBSTR (times,
0,
INSTR (times, ':') - 1
) AS times_exit,
tt.counts AS counts
FROM tt) a
GROUP BY a.times_exit
UNION ALL
SELECT TO_CHAR (d.days, 'yyyy-mm-dd') || ' ' || b.hours times_exit,
0 counts
FROM (SELECT LPAD (TO_CHAR (ROWNUM - 1), 2, '0') hours
FROM all_objects
WHERE ROWNUM <= 24) b,
(SELECT TO_DATE (&startdate, 'yyyy-mm-dd') + ROWNUM days
FROM all_objects
WHERE ROWNUM <=
TO_DATE (&enddate, 'yyyy-mm-dd')
- TO_DATE (&startdate, 'yyyy-mm-dd')) d)
ORDER BY times_exit)
GROUP BY times_exit参数输入:'2009-10-31','2009-11-01'
结果:
2009-11-01 00 30
2009-11-01 01 0
2009-11-01 02 0
2009-11-01 03 15
2009-11-01 04 0
2009-11-01 05 15
2009-11-01 06 0
2009-11-01 07 0
2009-11-01 08 0
2009-11-01 09 0
2009-11-01 10 0
2009-11-01 11 0
2009-11-01 12 0
2009-11-01 13 0
2009-11-01 14 0
2009-11-01 15 0
2009-11-01 16 0
2009-11-01 17 0
2009-11-01 18 0
2009-11-01 19 0
2009-11-01 20 0
2009-11-01 21 0
2009-11-01 22 0
2009-11-01 23 0
(
SELECT to_date('2009-11-01 00:12:26','yyyy-mm-dd hh24:mi:ss') times, 5 counts
FROM DUAL
UNION ALL
SELECT to_date('2009-11-01 00:12:26','yyyy-mm-dd hh24:mi:ss'), 15
FROM DUAL
UNION ALL
SELECT to_date('2009-11-01 00:22:26','yyyy-mm-dd hh24:mi:ss'), 10
FROM DUAL
UNION ALL
SELECT to_date('2009-11-01 05:22:26','yyyy-mm-dd hh24:mi:ss'), 15
FROM DUAL
UNION ALL
SELECT to_date('2009-11-01 03:12:26','yyyy-mm-dd hh24:mi:ss'), 15
FROM DUAL)
select to_char((to_date('2009-11-01', 'yyyy-mm-dd')+no1/24), 'yyyy-mm-dd hh24') "时间", nvl(b.cnt, 0) "数量"
from (select rownum-1 no1 from dual connect by rownum <=24) a left join
(select trunc(times, 'hh') d1, sum(counts) cnt from tt where trunc(times, 'dd')=to_date('2009-11-01', 'yyyy-mm-dd')
group by trunc(times, 'hh')) b on to_number(to_char(b.d1, 'hh24'))=a.no1