select identity(int,1,1) as nid,a into # from 表select b.id-a.id from # a,# b where a.nid+1=b.niddrop table #
create table T(a int)insert into T select 1 insert into T select 3 insert into T select 5 insert into T select 7select identity(int,1,1) as id,a into #t from Tselect a-(select a from #T where id=t1.id-1) from #T as t1 where id>1drop table T,#T
select x=(select min(a) from a where a>b.a)-a from a b
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) goSELECT a=(SELECT TOP 1 a FROM TB WHERE a>A.a order by a )-a FROM TB Adrop table tb/* a ----------- 2 2 2 NULL(所影响的行数为 4 行) */
--不过,最好使用临时表 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) goselect id = identity(int,1,1) , a into tmp from tbSELECT a = (SELECT TOP 1 a FROM tmp WHERE id > A.id order by id ) - a FROM tmp Adrop table tb , tmp/* a ----------- 2 2 2 NULL(所影响的行数为 4 行) */
create table T(a int)insert into T select 1
insert into T select 3
insert into T select 5
insert into T select 7select identity(int,1,1) as id,a
into #t
from Tselect a-(select a from #T where id=t1.id-1)
from #T as t1
where id>1drop table T,#T
insert into tb values(1)
insert into tb values(3)
insert into tb values(5)
insert into tb values(7)
goSELECT
a=(SELECT TOP 1 a FROM TB WHERE a>A.a order by a )-a
FROM TB Adrop table tb/*
a
-----------
2
2
2
NULL(所影响的行数为 4 行)
*/
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)
goselect id = identity(int,1,1) , a into tmp from tbSELECT
a = (SELECT TOP 1 a FROM tmp WHERE id > A.id order by id ) - a
FROM tmp Adrop table tb , tmp/*
a
-----------
2
2
2
NULL(所影响的行数为 4 行)
*/