存储过程应该很简单create proc pr_计算 @出库目标数 int as set nocount onupdate a set A2=(select sum(sh-A1) from TT where Sdate<a.Sdate or Sdate=a.Sdate and name <=a.name), A3=@出库目标数-(select sum(A1) from TT where Sdate<a.Sdate or Sdate=a.Sdate and name <=a.name) from TT ago
调用方法 exec pr_计算 1000
哦,有id 字段,改下 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
占个位置,时间有相同记录通过临时表:declare @ta table(Sdate datetime, name nvarchar(10), sh int, A1 int, A2 int, A3 int) 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 行) create proc test_p asselect *,ID=identity(int,1,1) into # from 表 order by sdate asc--先按时间排序select 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 # a
@出库目标数 int
as
set nocount onupdate a
set A2=(select sum(sh-A1) from TT where Sdate<a.Sdate or Sdate=a.Sdate and name <=a.name),
A3=@出库目标数-(select sum(A1) from TT where Sdate<a.Sdate or Sdate=a.Sdate and name <=a.name)
from TT ago
exec pr_计算 1000
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 行)
create proc test_p
asselect *,ID=identity(int,1,1) into # from 表 order by sdate asc--先按时间排序select 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
# a