日期:2014-05-18  浏览次数:20592 次

请教存贮过程的问题
PS13中的字段
pzhm               cpgg           cppz       sl       dfsl

2006-43         600             123         200

ps12中的字段
cpgg       cppz       package               sl    
600         123         2006-43/21         10
600         123         2006-43/24         34
600         123         2006-43/2           100
需要得到PS13中DFSL=144,现
update   ps13   set   dfsl=t.sl
from   ps13,(select     cppz,cpgg,package,sum(sl)   as   sl   from   ps12   group   by   cppz,cpgg,package)   as   t
where   ps13.cppz=t.cppz   and   ps13.cpgg=t.cpgg   and   (t.package   like   rtrim(pzhm)+ '% ')
得到的DFSL为44

------解决方案--------------------
update ps13 set dfsl=t.sl
from (select ps13.cppz,ps13.cpgg,ps13.pzhm,sum(sl) as sl from ps12,ps13
where ps13.cppz=t.cppz and ps13.cpgg=t.cpgg and (t.package like rtrim(pzhm)+ '% ')
group ps13.cppz,ps13.cpgg,ps13.pzhm
) as t
where ps13.cppz=t.cppz and ps13.cpgg=t.cpgg and ps13.pzhm=t.pzhm

*****************************************************************************
欢迎使用CSDN论坛专用阅读器 : CSDN Reader(附全部源代码)

最新版本:20070130

http://www.cnblogs.com/feiyun0112/archive/2006/09/20/509783.html
------解决方案--------------------
create table ps13 (pzhm varchar(10),cpgg int,cppz int,sl int,dfsl int)
insert into ps13(pzhm,cpgg,cppz,sl)
select '2006-43 ',600,123,200
go
create table ps12(cpgg int,cppz int,package varchar(20),sl int)
insert into ps12
select 600,123, '2006-43/21 ',10
union all select 600,123, '2006-43/24 ',34
union all select 600,123, '2006-43/2 ',100

update ps13 set dfsl=t.sl
from ps13,(select a.cppz,a.cpgg,b.pzhm pzhm,sum(a.sl) as sl from ps12 a join ps13 b on a.package like rtrim(pzhm)+ '% ' group by a.cppz,a.cpgg,b.pzhm) as t
where ps13.cppz=t.cppz and ps13.cpgg=t.cpgg and t.pzhm=ps13.pzhm

select * from ps13

drop table ps12,ps13