求一天中每小时的数据量-----这个SQL如何写,请牛人提供思路。
表A
字段 A B
2007-09-27 2:00 -3
2007-09-27 4:00 200
2007-09-27 4:00 -100
2007-09-27 17:00 -4
如何写这个SQL?得到的结果在页面上显示为:A按小时排序,B、C空值补0,如果B、C有值,则B显示正数,C显示负数。
A B C
2007-09-27 1:00 0 0
2007-09-27 2:00 0 -3
2007-09-27 3:00 0 0
2007-09-27 4:00 200 -100
........
2007-09-27 24:00 0 0
------解决方案--------------------oracle 10g才能执行:
SELECT b.dd a, DECODE (aa.b, NULL, 0, aa.b) b, DECODE (aa.c,
NULL, 0,
aa.c
) c
FROM (SELECT TO_CHAR (TRUNC (a, 'hh24 '), 'yyyy-mm-dd hh24:mi:ss ') a,
SUM (DECODE (SIGN (b), -1, 0, b)) b,
SUM (DECODE (SIGN (b), -1, b, 0)) c
FROM a
GROUP BY TRUNC (a, 'hh24 ')) aa,
(SELECT TO_CHAR ( TRUNC (TO_DATE ( '2007-09-27 ', 'yyyy-mm-dd '))
+ (LEVEL - 1) / 24,
'yyyy-mm-dd hh24:mi:ss '
) dd
FROM DUAL
CONNECT BY LEVEL < 25) b
WHERE b.dd = aa.a(+)
order by b.dd