跪求时间拆分存储过程，谢谢了

Question

本帖最后由 Grn0210 于 2013-11-21 15:29:11 编辑

Answer 1

先弄个临时表把跨天的拆分成几条记录吧，然后
select ID,min(StareTime),max(EndTime),sum(round((EndTime-StareTime)*24),0) from tb group by id就行了

Answer 2

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

Answer 3

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
              from (select max(et - st) + 1 gap from test)
            connect by rownum <= gap) b
     where a.et >= a.st + b.dis - 1
     order by h;
  c_tem   varchar2(10);
  i       number;
  l_date  date;
  l_count number;
begin
  for c1 in cr1 loop
    i := 0;
    select count(*)
      into l_count
      from test t
     where to_char(t.et, 'yyyy/mm/dd') = c1.h;
    if l_count > 0 then
      for c2 in (select * from test where to_char(et, 'yyyy/mm/dd') = c1.h) loop
        select count(*)
          into l_count
          from test t
         where t.st <= c2.et
           and to_char(t.st, 'yyyy/mm/dd') = to_char(c2.et, 'yyyy/mm/dd');
        if l_count > 0 then
          begin
            select max(st)
              into l_date
              from test t
             where t.st <= c2.et
               and to_char(t.st, 'yyyy/mm/dd') =
                   to_char(c2.et, 'yyyy/mm/dd');
          end;
        else
          l_date := to_date(c1.h || ' 00:00:01', 'yyyy/mm/dd hh24:mi:ss');
        end if;
        i := i + round((c2.et - l_date) * 24);
      end loop;
    else
      begin
        select t.st
          into l_date
          from test t
         where to_char(t.st, 'yyyy/mm/dd') = c1.h;
      exception
        when others then
          l_date := to_date(c1.h || ' 00:00:00', 'yyyy/mm/dd hh24:mi:ss');
      end;
      i := round((to_date(c1.h || ' 23:59:59', 'yyyy/mm/dd hh24:mi:ss') -
                 l_date) * 24);
    
    end if;
    dbms_output.put_line(c1.id || '  ' ||
                         to_date(c1.h || ' 00:00:01',
                                 'yyyy/mm/dd hh24:mi:ss') || '  ' ||
                         to_date(c1.h || ' 23:59:59',
                                 'yyyy/mm/dd hh24:mi:ss') || '  ' || i);
  end loop;end;

Answer 4

怎么弄临时表呢？那存储过程又怎么写呢？
不用临时表啊，我已经帮你写了啊
此sql 定义游标的时候
cursor cr1 is
    select distinct to_char(a.st + b.dis - 1, 'yyyy/mm/dd') h, a.id
      from test a,
           (select rownum dis
              from (select max(et - st) + 1 gap from test)
            connect by rownum <= gap) b
     where a.et >= a.st + b.dis - 1
     order by h;
不能查询出 像这样的数据（2013/5/7 20:12:33 至 2013/5/8 20:12:08）
可修改为  
cursor cr1 is
    select distinct to_char(a.st + b.dis - 1, 'yyyy/mm/dd') h, a.id
      from test a,
           (select rownum dis
              from (select max(et - st) + 1 gap from test)
            connect by rownum <= gap) b
     where to_char(a.et,'yyyy/mm/dd') >= to_char(a.st + b.dis - 1,'yyyy/mm/dd')
     order by h;还有数据得和id关联。这种数据 
001 2013/5/4 7:12:33 2013/5/4 17:07:52
001 2013/5/4 20:12:33 2013/5/5 22:00:00
统计也有问题、