求一个累计数据查询语句

[buller]

求一个MS SQL server查询语句
数据表：

 
bm		sl		
 A		1		
 B		2		
 A		3		
 A		-1		
 B		3		
 C		1		
 A		5		

需返回格式：

 
bm		sl		leiji
 A		1		1
 B		2		2
 A		3		4
 A		-1		3
 B		3		5
 C		1		1
 A		5		8

即第三列是同bm对应sl的累计

[唐诗三百首]

create table #t(bm varchar(10),sl int)

insert into #t 
 select 'A',1 union all
 select 'B',2 union all
 select 'A',3 union all
 select 'A',-1 union all
 select 'B',3 union all
 select 'C',1 union all
 select 'A',5


with t as
(select rn=row_number() over(order by getdate()),* from #t
)
select bm,sl,leiji=(select sum(b.sl) from t b where b.bm=a.bm and b.rn<=a.rn)
 from t a

/*
bm         sl          leiji
---------- ----------- -----------
A          1           1
B          2           2
A          3           4
A          -1          3
B          3           5
C          1           1
A          5           8

(7 行受影响)
*/

[砸死牛顿的苹果]

如果是sql2012以上版本也可以这样

 select bm,sl,sum(sl)over(partition by bm order by rid)leiji 
 from 
	(select *,rid=row_number()over(order by (select 1)) from  #t)t 
 order by rid

[buller]

引用 2 楼 砸死牛顿的苹果 的回复:

如果是sql2012以上版本也可以这样

 select bm,sl,sum(sl)over(partition by bm order by rid)leiji 
 from 
	(select *,rid=row_number()over(order by (select 1)) from  #t)t 
 order by rid

这个语法的2012版本以上啊，怪不得order那有语法错误，先用第一种方法吧，不知道大量数据速度咋样