表:T
字段:begin_time,end_time,t_result
值:2009-08-11 00:30:00 , 2009-08-11 01:00:00, 10
2009-08-11 01:00:00 , 2009-08-11 01:30:00, 20
2009-08-11 01:30:00 , 2009-08-11 02:00:00, 30
2009-08-11 02:00:00 , 2009-08-11 02:30:00, 10
2009-08-11 02:30:00 , 2009-08-11 03:00:00, 10
我要显示time,t_result
2009-08-11 01:00:00 10
2009-08-11 02:00:00 50
2009-08-11 03:00:00 20就是按每小时统计一次结果;
oracle 求SQL或思路
字段:begin_time,end_time,t_result
值:2009-08-11 00:30:00 , 2009-08-11 01:00:00, 10
2009-08-11 01:00:00 , 2009-08-11 01:30:00, 20
2009-08-11 01:30:00 , 2009-08-11 02:00:00, 30
2009-08-11 02:00:00 , 2009-08-11 02:30:00, 10
2009-08-11 02:30:00 , 2009-08-11 03:00:00, 10
我要显示time,t_result
2009-08-11 01:00:00 10
2009-08-11 02:00:00 50
2009-08-11 03:00:00 20就是按每小时统计一次结果;
oracle 求SQL或思路
group by trunc(endtime-1/24/60/60,'HH')
每小时自动统计可以通过job来实现
time需要进一个小时
select trunc(endtime-1/24/60/60,'HH')+1/24 time,sum(t_result)t_result from T
group by trunc(endtime-1/24/60/60,'HH')
因为统计1点的数据endtime实际上是2点
group by trunc(begin_time,'HH')
2 FROM T
3 GROUP BY ROUND(end_time,'HH');TIME T_RESULT
------------------- ----------
2009-08-11 01:00:00 10
2009-08-11 02:00:00 50
2009-08-11 03:00:00 20