sql 两行数据前后对比

[a409329879]

数据表 Test 字段id ，time
id time
1 2012.7.23
2 2012.7.30
3 ...
.. ...
请问要怎么才能够两行的时间进行比较计算时间差。就是说如果id=1 就和id=2 的比较，id=3 和id=4 比较。关键实际数据id相差不为1，可能相差为2,3,4.。。。

[人生无悔]

create table test(
id int primary key,
time date
);
insert into test values(1,to_date('2012.7.23','yyyy.mm.dd'));
insert into test values(3,to_date('2012.7.24','yyyy.mm.dd'));
insert into test values(5,to_date('2012.7.30','yyyy.mm.dd'));
insert into test values(7,to_date('2012.8.1','yyyy.mm.dd'));
insert into test values(8,to_date('2012.8.23','yyyy.mm.dd'));
insert into test values(9,to_date('2012.8.24','yyyy.mm.dd'));
insert into test values(11,to_date('2012.8.22','yyyy.mm.dd'));


select t1.id,t1.time,t2.time,t2.time-t1.time val
from 
(select rownum rn,id,time from test) t1,
(select rownum rn,id,time from test) t2
where t1.rn=t2.rn-1;

--或参照我之前写的更新
http://blog.csdn.net/zhangandli/article/details/7764852

[人生无悔]

你的好像要改一点才能符合要求，呵呵

select t1.id,t1.time,t2.time,t2.time-t1.time val
from 
(select rownum rn,id,time from test) t1,
(select rownum rn,id,time from test) t2
where t1.rn=t2.rn-1 and mod(t1.rn,2)=1;

[a409329879]

谢啦，虽然问题没有解决，但是很有启发。因为我用的是sybase，但是sybase没有rownum

[大明湖畔_帅锅]

应该可以用行数配合lag实现吧

[luckings]

就是跟数据编个序号，用相邻号求差值
select t1.id,t1.time,t2.time,t2.time-t1.time val
from 
(select rownum rn1,id,time from test) t1 inner join 
(select rownum rn2,id,time from test) t2
on t1.rn1=t2.rn2-1  ;

[ershihaidao]

create table test(
id int primary key,
time date
);
insert into test values(1,to_date('2012.7.23','yyyy.mm.dd'));
insert into test values(3,to_date('2012.7.24','yyyy.mm.dd'));
insert into test values(5,to_date('2012.7.30','yyyy.mm.dd'));
insert into test values(7,to_date('2012.8.1','yyyy.mm.dd'));
insert into test values(8,to_date('2012.8.23','yyyy.mm.dd'));
insert into test values(9,to_date('2012.8.24','yyyy.mm.dd'));
insert into test values(11,to_date('2012.8.22','yyyy.mm.dd'));

select id,time,time-pre_time from (
select id,time,lag(time) over (order by id) pre_time from test
)

[Aquarius_Uranus]

这样应该满足你的需求

with t as
(
select 1 id,date'2012-01-29' tdate from dual
union all
select 3,date'2012-02-03' from dual
union all
select 2,date'2012-01-28' from dual
union all
select 56,date'2012-02-09' from dual
union all
select 7,date'2012-02-05' from dual
)

select rn,id,tdate, decode(mod(rn,2),0 ,tdate-lag(tdate) over(order by rn))  from 
(select rownum rn,id,tdate from (
select id,tdate from t order by id))t1