select 编号
,(select sum(完成数)
from Table_A
where 编号=t.编号
and 完成日期<=t.完成日期
) as '完成数(累计)'
,完成日期
from Table_A t
order by 编号,完成日期
,(select sum(完成数)
from Table_A
where 编号=t.编号
and 完成日期<=t.完成日期
) as '完成数(累计)'
,完成日期
from Table_A t
order by 编号,完成日期
调试欢乐多
from 表 b
from 表 b
alter table table_a add id int identity(1,1) 然后调用下面的语句就可以实现你的要求了
select b.编号,b.完成日期,(a.sum-isnull((select sum(完成数) from table_a c where c.编号=b.编号 and c.id>b.id),0)) as 完成数(累加) from table_a b ,(
select 编号,sum(完成数) as sum from table_a group by 编号 ) a
where a.编号=b.编号
如果不唯一就给表增加一个字段为自动增长的列
alter table table_a add id int identity(1,1) 然后调用下面的语句就可以实现你的要求了
select b.编号,b.完成日期,(a.sum-isnull((select sum(完成数) from table_a c where c.编号=b.编号 and c.id>b.id),0)) as 完成数(累加) from table_a b ,(
select 编号,sum(完成数) as sum from table_a group by 编号 ) a
where a.编号=b.编号
完成数 int,
完成日期 smalldatetime)
insert into tb
select 'A001',800,'2005/10/2' union all
select 'A001',800,'2005/10/9' union all
select 'A001',400,'2005/10/13' union all
select 'A002',750,'2005/10/3' union all
select 'A003',300,'2005/10/2' union all
select 'A003',300,'2005/10/10' union all
select 'A003',250,'2005/10/12'
select 编号,
[完成数(累计)]=(select sum(完成数) from tb where 编号=a.编号 and 完成日期<=a.完成日期),
完成日期=convert(varchar(10),完成日期,111)
from tb a
/*
编号 完成数(累计) 完成日期
=================================
A001 800 2005/10/02
A001 1600 2005/10/09
A001 2000 2005/10/13
A002 750 2005/10/03
A003 300 2005/10/02
A003 600 2005/10/10
A003 850 2005/10/12
*/
drop table tb
insert @tb
select 'A001', 800, '2005/10/2' union all
select 'A001', 800, '2005/10/9' union all
select 'A001', 400, '2005/10/13' union all
select 'A002', 750, '2005/10/3' union all
select 'A003', 300, '2005/10/2' union all
select 'A003', 300, '2005/10/10' union all
select 'A003', 250, '2005/10/12'select 编号
,(select sum(完成数)
from @tb
where 编号=t.编号
and datediff(second,完成日期,t.完成日期) >=0
) as '完成数(累计)'
,完成日期
from @tb t
order by 编号,完成日期/*
编号 完成数(累计) 完成日期
-------------------------------------------------- ----------- ------------------------------------------------------
A001 800 2005-10-02 00:00:00.000
A001 1600 2005-10-09 00:00:00.000
A001 2000 2005-10-13 00:00:00.000
A002 750 2005-10-03 00:00:00.000
A003 300 2005-10-02 00:00:00.000
A003 600 2005-10-10 00:00:00.000
A003 850 2005-10-12 00:00:00.000
*/