继续:SQL语句逐层累计数量

[erp1998]

A B
a 1
a 2
a 3
a 10

要得到结果

a 1
a 3
a 6
a 16

已有网友解决如下:
select IDENTITY(int, 1, 1) as id,* into #tbl from tbl
select A,B=(Select sum(B) from tbl where A=x.A and id<=x.id)
from #tbl as x

如果变下

A B
a 1
a 2
a 3
b 20 *
b 9
c 9
c 6

要得到结果

a 1
a 3
a 6
b 20
b 29
c 9
c 15

要分组解决,在不用游标,单用语句不知有何好办法

[子陌红尘]

declare @t table(A varchar(10),B int)
insert into @t select 'a',1
insert into @t select 'a',2
insert into @t select 'a',3
insert into @t select 'b',20
insert into @t select 'b',9
insert into @t select 'c',9
insert into @t select 'c',6

declare @a varchar(10),@b int
update @t
set
@b=case when A!=isnull(@a,'') then B else B+@b end,
@a=A,
B = @b

select * from @t
/*
A B
---------- -----------
a 1
a 3
a 6
b 20
b 29
c 9
c 15
*/

[子陌红尘]

declare @t table(A varchar(10),B int)
insert into @t select 'a',1
insert into @t select 'a',2
insert into @t select 'a',3
insert into @t select 'b',20
insert into @t select 'b',9
insert into @t select 'c',9
insert into @t select 'c',6


declare @a table(id int identity(1,1),A varchar(10),B int)
insert into @a select * from @t

select a.A,B=sum(b.B) from @a a,@a b where a.A=b.A and a.ID>=b.ID group by a.ID,a.A

/*
A B
---------- -----------
a 1
a 3
a 6
b 20
b 29
c 9
c 15
*/

[lxw99]

libin_ftsafe(子陌红尘) ( )大哥，俺崇拜你。。。。。。。。。

[bugchen888]

五体投地,update居然玩到这种地步.....5555555........白活了.........

[erp1998]

确实是高手,佩服!!!