保存了一个累加的,先贴上来。 --------------------------------------create table test (c1 int, c2 char(1))insert test (c1,c2) select 2,'a' union all select 4,'b' union all select 5,'c' union all select 9,'d' select * from test select (select sum(c1) from test where c1<=a.c1) as c1,c2 from test adrop table test
--没有环境,请自行测试,当ID为连续时update a set a1=(select a1-isnull(a.a1,0) from tablea where id=a.id+1) from tablea a--select * from tablea
create table test (c1 int, c2 int)insert test (c1 ,c2 ) select 1,3 union all select 2,6 union all select 3,6 union all select 4,7 --用个临时表 select id=identity(int,1,1), * into # from test select c1,c2, case id when 1 then c2 else c2-(select c2 from # where id=a.id-1) end as col2 from # adrop table test , #
declare @t table(id int,a1 int) insert @t select 1,3 union all select 2,6 union all select 3,6 union all select 4,7 ----当ID连续时 select id,a1,a2 = a1 - case when id = 1 then 0 else (select a1 from @t where id = a.id -1) end from @t a ----当ID不连续时 select id,a1,a2 = a1 - case when id = 1 then 0 else (select top 1 a1 from @t where id < a.id order by id desc) end from @t a
--------------------------------------create table test
(c1 int,
c2 char(1))insert test (c1,c2)
select 2,'a' union all
select 4,'b' union all
select 5,'c' union all
select 9,'d' select * from test
select (select sum(c1) from test where c1<=a.c1) as c1,c2 from test adrop table test
set a1=(select a1-isnull(a.a1,0) from tablea where id=a.id+1)
from tablea a--select * from tablea
(c1 int,
c2 int)insert test (c1 ,c2 )
select 1,3 union all
select 2,6 union all
select 3,6 union all
select 4,7 --用个临时表
select id=identity(int,1,1), * into # from test
select c1,c2, case id when 1 then c2 else c2-(select c2 from # where id=a.id-1) end as col2 from # adrop table test , #
insert @t
select 1,3 union all
select 2,6 union all
select 3,6 union all
select 4,7
----当ID连续时
select id,a1,a2 = a1 - case when id = 1 then 0 else (select a1 from @t where id = a.id -1) end from @t a
----当ID不连续时
select id,a1,a2 = a1 - case when id = 1 then 0 else (select top 1 a1 from @t where id < a.id order by id desc) end from @t a