已知
表1
ID StareTime EndTime
001 2013/5/4 7:12:33 2013/5/4 17:07:52
001 2013/5/4 20:12:33 2013/5/4 22:00:00
001 2013/5/5 0:00:01 2013/5/14 23:59:59
001 2013/5/15 8:53:20 2013/5/16 20:35:23
求：按天来统计ID运行小时(把时间段进行拆分)
结果：
ID StareTime EndTime RunTime(小时)
001 2013/5/4 00:00:01 2013/5/4 23:59:59 12
001 2013/5/5 00:00:01 2013/5/5 23:59:59 24
001 2013/5/6 00:00:01 2013/5/6 23:59:59 24
.
.
.
001 2013/5/15 00:00:01 2013/5/15 23:59:59 15
001 2013/5/16 00:00:01 2013/5/16 23:59:59 21
------解决方案--------------------
先弄个临时表把跨天的拆分成几条记录吧，然后
select ID,min(StareTime),max(EndTime),sum(round((EndTime-StareTime)*24),0) from tb group by id就行了
------解决方案--------------------
create table t1 (id varchar2(20),StartTime date,EndTime date);
insert into t1
select '001' ID,To_date('2013/5/4 7:12:33','yyyy-mm-dd hh24:mi:ss') StareTime ,To_date('2013/5/4 17:07:52','yyyy-mm-dd hh24:mi:ss') EndTime from dual
union all select '001',To_date('2013/5/4 20:12:33','yyyy-mm-dd hh24:mi:ss') ,To_date('2013/5/4 22:00:00','yyyy-mm-dd hh24:mi:ss') from dual
union all select '001',To_date('2013/5/5 0:00:01','yyyy-mm-dd hh24:mi:ss') ,To_date('2013/5/14 23:59:59','yyyy-mm-dd hh24:mi:ss') from dual
union all select '001',To_date('2013/5/15 8:53:20','yyyy-mm-dd hh24:mi:ss') ,To_date('2013/5/16 20:35:23','yyyy-mm-dd hh24:mi:ss') from dual;
WITH t AS
(
SELECT To_date('2013/5/4 00:00:01','yyyy-mm-dd hh24:mi:ss') StartTime,To_date('2013/5/4 23:59:59','yyyy-mm-dd hh24:mi:ss')EndTime FROM dual
),dimDate AS
(
SELECT LEVEL AS rn, StartTime+LEVEL-1 StartTime ,EndTime+LEVEL-1 EndTime
FROM t
CONNECT BY LEVEL<=100
)
SELECT t.id,d.StartTime,d.EndTime
,SUM(
ROUND(TO_Number(
CASE
WHEN d.StartTime<=t.StartTime AND d.EndTime>=t.StartTime AND d.EndTime<=t.EndTime THEN d.EndTime-t.StartTime
WHEN d.StartTime<=t.StartTime AND d.EndTime>=t.EndTime THEN t.EndTime-t.StartTime
WHEN d.StartTime>t.StartTime AND d.StartTime<=t.EndTime AND t.EndTime<d.EndTime THEN t.EndTime-d.StartTime
WHEN d.StartTime>=t.StartTime AND d.StartTime<=t.EndTime AND t.EndTime>=d.EndTime THEN d.EndTime-d.StartTime
END
) * 24))
FROM dimDate d
JOIN t1 t
ON (d.StartTime<=t.StartTime AND d.EndTime>=t.StartTime AND d.EndTime<=t.EndTime)
OR (d.StartTime>=t.StartTime AND d.StartTime<=t.EndTime)
OR (d.StartTime<=t.StartTime AND d.EndTime>=t.EndTime)
GROUP BY t.id,d.StartTime,d.EndTime
ORDER BY d.StartTime
------解决方案--------------------
declare
cursor cr1 is
select distinct to_char(a.st + b.dis - 1, 'yyyy/mm/dd') h, a.id
from test a,
(select rownum dis