已知表:
Name Qty
A 100
B 200
C 300
B 400
A 500
C 600
A 700
要求逐行从前向后累加,想得到:
Name Qty TTL
A 100 100
B 200 200
C 300 300
B 400 600
A 500 600
C 600 900
A 700 1300
有无好的方法?谢了.
---------------------------------------------------------------
如果没有其他字段：
select Name,Qty,IDENTITY ( int) AS Id into #Temp from tablename
select Name,Qty,(select sum(Qty) from #Temp where name=a.name and Id<=a.Id) as TTL
from #Temp a
---------------------------------------------------------------
典型的数据累加问题。
如果有一个PID(IDENTITY int)字段，就只要一句够了：
select Name,Qty,(select sum(Qty) from TableName where name=a.name and PId<=a.PId) as TTL
from TableName a
---------------------------------------------------------------
select b.name,b.qty,(select sum(a.qty)
from (select name,qty,identity(int) as id into temp
from 已知表) a
where a.name=b.name and a.id<=b.id)
from (select name,qty,identity(int) as id into temp
from 已知表) b
---------------------------------------------------------------
should be this result sum row by row
Name Qty TTL
------------------------------ ----------- -----------
A 100 100
B 200 300
C 300 600
D 400 1000
E 500 1500
F 600 2100
F 700 2800
(7 row(s) affected)
select Name,Qty,(select sum(Qty) from Table1 where PId<=a.PId) as TTL
from Table1 a