一个表A(ID,date,ton):date-日期，ton-日产量(假设每天都为20,我的实际数值不一定就为20)
欲求月的累计产量
例如：
ID date ton
1 2003-11-1 20
2 2003-11-2 20
3 2003-11-3 20
… …… …
30 2003-11-30 20
想由一SQL语句得到的结果是：
date 累计ton
2003-11-1 20
2003-11-2 40
2003-11-3 60
…… …
也就是说2号的"累计ton" 的值是表A种1,2号中ton列值的和
3号"累计ton" 是1,2,3号 表A中ton的值相加
不知道我说清楚了没有，请各位给个解题方法或思路，谢谢
---------------------------------------------------------------
select date,(select sum(ton) from 表 where date<=a.date) as 累计ton
from 表 a
---------------------------------------------------------------
declare @table table(id int identity,date datetime,ton int)
insert @table values('2003-11-1',20)
insert @table values('2003-11-2',30)
insert @table values('2003-11-3',40)
select date,(select sum(ton) from @table where date<=a.date) ton
from @table a
/*
date ton
------------------------------------------------------ -----------
2003-11-01 00:00:00.000 20
2003-11-02 00:00:00.000 50
2003-11-03 00:00:00.000 90
*/
---------------------------------------------------------------
--或
declare @table table(id int identity,date datetime,ton int)
insert @table values('2003-11-1',20)
insert @table values('2003-11-2',30)
insert @table values('2003-11-3',40)
select a.date,sum(b.ton) ton
from @table a join @table b
on a.date >=b.date
group by a.date
/*
date ton
------------------------------------------------------ -----------
2003-11-01 00:00:00.000 20
2003-11-02 00:00:00.000 50
2003-11-03 00:00:00.000 90
*/
---------------------------------------------------------------
用到游标，也可把下面这些代码作为一个存储过程！
if exists(select * from sysobjects where name='tb_temp' and xtype='u') drop table tb_temp
select * into tb_temp from A order by ID
declare @ID_csr int
declare @ton_csr int
declare @sum int
set @sum=0
declare csr_mycsr cursor for
select ID,ton from A order by ID
open csr_mycsr
fetch next from csr_mycsr into @ID_csr,@ton_csr
while @@fetch_status=0
begin
set @sum=@sum+@ton_csr
update tb_temp set ton=@sum where ID=@ID_csr
fetch next from csr_mycsr into @ID_csr,@ton_csr
end
close csr_mycsr
DEALLOCATE csr_mycsr
select * from tb_temp
if exists(select * from sysobjects where name='tb_temp' and xtype='u') drop table tb_temp