如何在新建记录的同时计算累计值？

Question

假设表结构如下：
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
......
即在插入一条新记录时，自动计算出同一ORGID的累计值。用触发器可以不？如何实现？

Answer 1

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
*/这个就是统计数据，你可以给它写到触发器里面

Answer 2

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

Answer 3

可用触发器完成.
create trigger my_trig on tb for insert
as
begin
  update tb set totalAmount = i.Amount + isnull((select top 1 totalamount from tb where orgID = i.orgID and date < i.date order by date desc),0) from tb , i
  where tb.orgID = i.orgID and tb.date = i.date
end

Answer 4

;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

Answer 5

首先在這個表裡面加一個批次號 BatchNo之類的唯一值.
insert的後面調用個spcreate 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 endid 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
......