sql 两行数据前前后后对比

sql 两行数据前后对比
数据表 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.。。。

[解决办法]
你的好像要改一点才能符合要求，呵呵

SQL code

select t1.id,t1.time,t2.time,t2.time-t1.time valfrom (select rownum rn,id,time from test) t1,(select rownum rn,id,time from test) t2where t1.rn=t2.rn-1 and mod(t1.rn,2)=1;
[解决办法]
应该可以用行数配合lag实现吧
[解决办法]
SQL code
就是跟数据编个序号，用相邻号求差值select t1.id,t1.time,t2.time,t2.time-t1.time valfrom (select rownum rn1,id,time from test) t1 inner join (select rownum rn2,id,time from test) t2on t1.rn1=t2.rn2-1  ;
[解决办法]
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
)
[解决办法]
这样应该满足你的需求
SQL code
with t as(select 1 id,date'2012-01-29' tdate from dualunion allselect 3,date'2012-02-03' from dualunion allselect 2,date'2012-01-28' from dualunion allselect 56,date'2012-02-09' from dualunion allselect 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