临时表的使用问题

[想飞的狼]

create table #d(PLH CHAR(14),DJ_SN INT,spid char(10),ZHY char(8),zhyy char(14),RKSL numeric(9,0),cKSL numeric(9,0),JCSL numeric(9,0))
insert into #d(plh,dj_sn,spid,zhy,zhyy,rksl,cksl,jcsl)values('z001','1','s001','入','','100','0','100')
insert into #d(plh,dj_sn,spid,zhy,zhyy,rksl,cksl,jcsl)values('z002','2','s002','入','','100','0','100')
insert into #d(plh,dj_sn,spid,zhy,zhyy,rksl,cksl,jcsl)values('z003','3','s002','出','z002','0','50','100')
--PLH，唯一的序列，SPID 商品编号，ZHY 出入类型，ZHYY，当出时用来记相对应入的PLH，RKSL,入库数量，CKSL，出库数量，JCSL，结存数量，最后想把这个结存数量更新，也就是入库数量-所有出的时ZHYY对应的PLH
--这是一个入出库的表，每次销售都会把入时的凭单号（PLH）记录到出的ZHYY中，然后在相应的入的JCSL中减去出的，如何算出每一笔入库，对应出了多少，剩多少，偶用的是SQL2000

[an410398183]

create table #d
(PLH CHAR(14) --uniquer
,DJ_SN INT
,spid char(10) --product num
,ZHY nchar(8) --ZHY 出入类型，
,zhyy char(14) -- 当出时用来记相对应入的PLH，
,RKSL numeric(9,0) --RKSL,入库数量，
,cKSL numeric(9,0) --CKSL，出库数量，
,JCSL numeric(9,0) --JCSL，结存数量，
)
go

insert into #d
(plh,dj_sn,spid,zhy,zhyy,rksl,cksl,jcsl)
values('z001','1','s001',N'入','','100','0','100')
,('z002','2','s002',N'入','','100','0','100')
,('z004','4','s002',N'入','','80','0','80')
,('z003','3','s002',N'出','z002','0','50','100')
,('z005','5','s002',N'出','z004','0','50','100')
go

SELECT
*
,(SELECT ISNULL(SUM(RKSL),0) FROM #d WHERE ZHY=N'入' AND D.spid=spid) AS SUM_RU
,(SELECT ISNULL(SUM(cKSL),0) FROM #d WHERE ZHY=N'出' AND D.spid=spid) AS SUM_CHU
,(SELECT ISNULL(SUM(RKSL),0) FROM #d WHERE ZHY=N'入' AND D.spid=spid) -(SELECT ISNULL(SUM(cKSL),0) FROM #d WHERE ZHY=N'出' AND D.spid=spid) AS Sheng
FROM #d D

[AcHerat]

update t
set jcsl = jcsl + (select sum(pksl-cksl) from tb where plh <= t.plh)
from tb t


类似这样，要找一个可以区分前后顺序的列来更新。

[想飞的狼]

谢谢，偶研究下