17,377
社区成员
发帖
与我相关
我的任务
分享
select b.time2 begintime, a.time1 endtime, (b.time2 - a.time1)*24*60||' minutes' res
from
(select id, a, time1, time2 , rownum as cnt from test_yixl order by time1 desc) a,
(select id, a, time1, time2 , rownum as cnt from test_yixl order by time1 desc) b
where b.cnt + 1 = a.cnt;
with tb as(
select 12 ID, 'D203456700' A, '2012-03-19 10:08:01' Time1,'2012-03-19 10:10:01' Time2 from dual union all
select 11, 'D203456600' ,'2012-03-19 10:06:01' ,'2012-03-19 10:08:01' from dual union all
select 9 ,'D203456500' ,'2012-03-19 10:04:01' ,'2012-03-19 10:06:01' from dual union all
select 8 ,'D203456300' ,'2012-03-19 10:02:01' ,'2012-03-19 10:04:01' from dual)
select prior to_date(time2,'yyyy-mm-dd hh24:mi:ss')- to_date(time1,'yyyy-mm-dd hh24:mi:ss')
from tb start with Time2='2012-03-19 10:10:01' connect by Time2=prior Time1