exec cs#'$tbl
id orgID date Amount
1 1001 2012-2-1 20
2 1002 2012-2-1 10
3 1003 2012-2-1 5
4 1001 2012-2-2 10
5 1002 2012-2-3 10
6 1003 2012-2-4 5
'
--> 测试数据：[tbl]
if object_id('[tbl]') is not null 
drop table [tbl]
go
create table [tbl](
[id] int,
[orgID] int,
[date] datetime,
[Amount] int
)
go
insert [tbl]
select 1,1001,'2012-2-1',20 union all
select 2,1002,'2012-2-1',10 union all
select 3,1003,'2012-2-1',5 union all
select 4,1001,'2012-2-2',10 union all
select 5,1002,'2012-2-3',10 union all
select 6,1003,'2012-2-4',5


;with t
as(
select ROW_NUMBER()Over(PARTITION by [orgID] order by getdate())as num,
*,Amount as total from tbl
),
m
as(
select num,id,orgID,[date],Amount,total from t where num=1
union all
select a.num,a.id,a.orgID,a.[date],a.Amount,b.total+a.Amount from t a
inner join m b
on a.num=b.num+1 and a.orgID=b.orgID
)
select id, orgID, [date], Amount, total from m order by orgID
/*
id    orgID    date    Amount    total
1    1001    2012-02-01 00:00:00.000    20    20
4    1001    2012-02-02 00:00:00.000    10    30
2    1002    2012-02-01 00:00:00.000    10    20
5    1002    2012-02-03 00:00:00.000    10    10
3    1003    2012-02-01 00:00:00.000    5    5
6    1003    2012-02-04 00:00:00.000    5    10
*/

这个就是统计数据，你可以给它写到触发器里面

------解决方案--------------------
探讨


这么复杂啊？！谢谢指导。

不过我不求现有数据的累计值，而是在每插入一条新记录的时候计算出累计值并保存到表中。也就是同一ORGID前一条记录的的totalAmount  + 新记录的Amount


------解决方案--------------------
探讨

假设表结构如下：
id orgID date Amount totalAmount
1 1001 2012-2-1 20 20
2 1002 2012-2-1 10 10
3 1003 2012-2-1 5 5
4 1001 2012-2-2 10 30
5 1002 2012-2-3 10 20
6 1003 2012-2-4 5 10
......
即在插入一条新记录时，自动……


------解决方案--------------------
一般来讲，计算量最好不要保存在表里，假如中间有一条记录的值发生变化，那后面的值是不是都会跟着要发生变化

你的问题可以用触发器解决，可以用前触发，也可以用后触发

但我还是倾向于查询的时候再统计

------解决方案--------------------
;with t
as(
select ROW_NUMBER()Over(PARTITION by [orgID] order by getdate())as num,
*,Amount as total from tbl
),


为什么是order by getdate()，为什么不是  order by date

------解决方案--------------------
首先在這個表裡面加一個批次號 BatchNo之類的唯一值.
insert的後面調用個sp
SQL code

create PROCEDURE xxx_SP
@BatchNo    NVARCHAR(50)
AS
declare @orgID nvarchar(10)
declare @Cur cursor 
begin
 SET @Cur = CURSOR FOR (SELECT orgID FROM dt where BatchNo=@BatchNo)
        OPEN @Cur
        FETCH NEXT FROM @Cur INTO @orgID 
        WHILE(@@FETCH_STATUS = 0)
        BEGIN  
                update dt set totalAmount = select sum (Amount ) from dt where orgID=@orgID group by Amount where BatchNo=@BatchNo and orgID=@orgID 

end