请教：如何用SQL实现查询表里的下一条数据减去上一条数据

[wangjunzhe321]

例如：
开始时间 结束时间
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或者函数怎么实现？

[小海葵1]

给你个例子 WITH TEST AS( SELECT 1 AS N1,2 AS N2 FROM DUAL UNION ALL SELECT 3 AS N1,4 AS N2 FROM DUAL UNION ALL SELECT 5 AS N1,6 AS N2 FROM DUAL ) SELECT N1 - LAG(N2,1,0)OVER(ORDER BY N2) FROM TEST

[izj]

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改为日期相减哈。。

[wangjunzhe321]

引用 2 楼 New_sara 的回复:

SQL code?1234567891011121314151617181920with 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……

您的回复对我很有用 ，但是我所查的表里很多数据，我不能每条都写个UNION吧？那要写很多啊

[izj]

引用 3 楼 wangjunzhe321 的回复:

引用 2 楼 New_sara 的回复:SQL code?1234567891011121314151617181920with 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……

汗。。。。你没发现那test （临时表）表指代的就是你要查询的那张表 select 1000 as id , 300 as mid from dual

[sky_08_06_02]

引用 2 楼 New_sara 的回复:

SQL code ? 12345678910111213141516171819202122 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 ……

正解，利用rownum和左连接解决问题

[aaronsuLoveM]

大神能说的再详细点吗？看不太懂