求一列数据之间的差

[giftzheng]

例 ：
有个数字字段a
a
1
3
5
7

如何得到他们之间的差
2
2
2

[子陌红尘]

select identity(int,1,1) as nid,a into # from 表

select b.id-a.id from # a,# b where a.nid+1=b.nid

drop table #

[OracleRoob]

create table T(a int)

insert into T select 1
insert into T select 3
insert into T select 5
insert into T select 7

select identity(int,1,1) as id,a
into #t
from T

select a-(select a from #T where id=t1.id-1)
from #T as t1
where id>1

drop table T,#T

[chuifengde]

select x=(select min(a) from a where a>b.a)-a from a b

[dawugui]

create table tb(a int)
insert into tb values(1)
insert into tb values(3)
insert into tb values(5)
insert into tb values(7)
go

SELECT 
 a=(SELECT TOP 1 a FROM TB WHERE a>A.a order by a )-a
FROM TB A

drop table tb

/*
a           
----------- 
2
2
2
NULL

（所影响的行数为 4 行）
*/

[dawugui]

--不过,最好使用临时表
create table tb(a int)
insert into tb values(1)
insert into tb values(3)
insert into tb values(5)
insert into tb values(7)
go

select id = identity(int,1,1) , a into tmp from tb

SELECT 
 a = (SELECT TOP 1 a FROM tmp WHERE id > A.id order by id ) - a
FROM tmp A

drop table tb , tmp

/*
a           
----------- 
2
2
2
NULL

（所影响的行数为 4 行）
*/