select Sdate,name,sh,A1
,(select sum(sh-A1) from TT where Sdate<a.Sdate or Sdate=a.Sdate and name <=a.name) as A2
,1000-(select sum(sh-A1) from TT where Sdate<a.Sdate or Sdate=a.Sdate and name <=a.name) as A3
from TT a
order by a.Sdate,a.name
,(select sum(sh-A1) from TT where Sdate<a.Sdate or Sdate=a.Sdate and name <=a.name) as A2
,1000-(select sum(sh-A1) from TT where Sdate<a.Sdate or Sdate=a.Sdate and name <=a.name) as A3
from TT a
order by a.Sdate,a.name
,(select sum(sh-A1) from TT where Sdate<a.Sdate or Sdate=a.Sdate and name <=a.name) as A2
,1000-(select sum(A1) from TT where Sdate<a.Sdate or Sdate=a.Sdate and name <=a.name) as A3
from TT a
order by a.Sdate,a.name
有如下表 TT 日期 名称 入库 出库 结余 欠出库目标数
字段 id Sdate name sh A1 A2 A3
记录 1 2007/07/07 QQ 20 5 0 0
2 2007/07/08 SS 40 30 0 0
3 2007/07/08 WW 500 490 0 0
4 2007/07/09 DD 90 0 0 0
5 2007/07/10 YY 0 80 0 0计算得到结果
日期 名称 入库 出库 结余 欠出库目标数
字段 id Sdate name sh A1 A2 A3
记录 1 2007/07/07 QQ 20 5 15 995
2 2007/07/08 SS 40 30 25 965
3 2007/07/08 WW 500 490 35 475
4 2007/07/09 DD 90 0 125 475
5 2007/07/10 YY 0 80 45 395
set A2=(select sum(sh-A1) from TT where Sdate<a.Sdate or Sdate=a.Sdate and name <=a.name),
A3=1000-(select sum(A1) from TT where Sdate<a.Sdate or Sdate=a.Sdate and name <=a.name)
from TT a
不應該用name來排序吧,是不是應該加一個標識列
--------------------------------------------
说得有理,关键看搂主的name是什么情况,如果保证name非空且Sdate,name 唯一就可以不改,否则需要用临时表加一个標識列
create proc pr_计算
@出库目标数 int
as
set nocount onupdate a
set A2=(select sum(sh-A1) from TT where id<=a.id),
A3=@出库目标数-(select sum(A1) from TT where id<=a.id)
from TT ago
insert @ta select '2007/07/07', 'QQ', 20, 5, 0, 0
union all select '2007/07/08', 'SS', 40, 30, 0, 0
union all select '2007/07/08', 'WW', 500, 490, 0, 0
union all select '2007/07/09', 'DD', 90, 0, 0, 0
union all select '2007/07/10', 'YY', 0, 80, 0, 0select *,ID=identity(int,1,1) into # from @ta order by sdate ascselect Sdate,name,sh,A1,A2=(select sum(sh-A1) from # where ID!>a.ID ),
A3=(select 1000-sum(A1) from # where ID!>a.ID)
from
# aSdate name sh A1 A2 A3
------------------------------------------------------ ---------- ----------- ----------- ----------- -----------
2007-07-07 00:00:00.000 QQ 20 5 15 995
2007-07-08 00:00:00.000 SS 40 30 25 965
2007-07-08 00:00:00.000 WW 500 490 35 475
2007-07-09 00:00:00.000 DD 90 0 125 475
2007-07-10 00:00:00.000 YY 0 80 45 395(所影响的行数为 5 行)