同意楼上的观点!可以用 select id=identity(int,1,1),* into # from 表 来生成临时表 举个例declare @t table (col int)insert @t select 1 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 4select id=identity(int,1,1),* into # from @tselect *,(select min(col) from # a where a.id>b.id)-col from # bdrop table #id col ----------- ----------- ----------- 1 1 0 2 1 1 3 2 1 4 3 1 5 4 0 6 5 -1 7 4 NULL(所影响的行数为 7 行)
declare @t table (id int) insert into @t select 1 union all select 2 union all select 3 union all select 5 union all select 7 --方法一 select isnull(abs(a.id-(select top 1 b.id from @t b where a.id>b.id order by b.id desc)),0) as a4 from @T a order by a.id/* a4 ----------- 0 1 1 2 2*/
--方法二declare @t table (id int) insert into @t select 1 union all select 2 union all select 3 union all select 5 union all select 7declare @ld_c int declare @ld_b int select t.id into #tmp from @t t order by t.id update #tmp set @ld_b=@ld_c,@ld_c=id,id=isnull(abs(@ld_b-@ld_c),0)select * from #tmp drop table #tmp/*id ----------- 0 1 1 2 2*/
create table a(id int) insert a select 1 union all select 2 union all select 3 union all select 4 union all select 5 下一行减去前一行select id1,id2,id=id2-id1 from (select id1=s.id,id2=min(t.id) from a s join a t on s.id<t.id group by s.id)rid1 id2 id ----------- ----------- ----------- 1 2 1 2 3 2 3 4 3 4 5 4(所影响的行数为 4 行)
各行减去第一行select id1,id2,id=id2-(select min(id1) from (select id1=s.id,id2=min(t.id) from a s join a t on s.id<t.id group by s.id)r) from (select id1=s.id,id2=min(t.id) from a s join a t on s.id<t.id group by s.id)rid1 id2 id ----------- ----------- ----------- 1 2 1 2 3 1 3 4 1 4 5 1(所影响的行数为 4 行)
数据不一定是连续的吧
1
2
3
4
5要求是下一行减去前一行,即得出以下数据:
1
2
3
4
???
好象结果不是减去前一行的值啊
要是的话应该结果为
1
1
1
1
啊
和取值来写sql
select id=identity(int,1,1),* into # from 表 来生成临时表
举个例declare @t table
(col int)insert @t
select 1 union all
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 4select id=identity(int,1,1),* into # from @tselect *,(select min(col) from # a where a.id>b.id)-col from # bdrop table #id col
----------- ----------- -----------
1 1 0
2 1 1
3 2 1
4 3 1
5 4 0
6 5 -1
7 4 NULL(所影响的行数为 7 行)
insert into @t select 1
union all select 2
union all select 3
union all select 5
union all select 7
--方法一
select isnull(abs(a.id-(select top 1 b.id from @t b where a.id>b.id order by b.id desc)),0) as a4
from @T a
order by a.id/*
a4
-----------
0
1
1
2
2*/
insert into @t select 1
union all select 2
union all select 3
union all select 5
union all select 7declare @ld_c int
declare @ld_b int
select t.id
into #tmp
from @t t
order by t.id
update #tmp set @ld_b=@ld_c,@ld_c=id,id=isnull(abs(@ld_b-@ld_c),0)select * from #tmp
drop table #tmp/*id
-----------
0
1
1
2
2*/
insert a
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
下一行减去前一行select id1,id2,id=id2-id1 from (select id1=s.id,id2=min(t.id) from a s join a t on s.id<t.id
group by s.id)rid1 id2 id
----------- ----------- -----------
1 2 1
2 3 2
3 4 3
4 5 4(所影响的行数为 4 行)
group by s.id)r)
from
(select id1=s.id,id2=min(t.id) from a s join a t on s.id<t.id
group by s.id)rid1 id2 id
----------- ----------- -----------
1 2 1
2 3 1
3 4 1
4 5 1(所影响的行数为 4 行)