例如:
开始时间 结束时间
1 2012-11-01 19:00:00 2012-11-02 00:00:00
2 2012-11-15 12:30:00 2012-11-20 00:34:00
我想实现第二条记录的开始时间减去第一条记录的结束时间,可能是多条记录,多条记录要实现下一条记录的开始时间减去上一条记录的结束时间,请问用sql或者函数怎么实现?
------解决方案--------------------
with
test as
(
select 1000 as id , 300 as mid from dual
union all
select 1000 as id , 200 as mid from dual
union all
select 1000 as id , 200 as mid from dual
),
test1 as
(
select rownum rn , a.* from test a
)
select a.id - b.mid as result from test1 a , test1 b where b.rn+1 = a.rn(+) order by a.rn
--运行结果
--------------------
700
800
null
你自己把上面的id改为日期相减哈。。