27,580
社区成员
发帖
与我相关
我的任务
分享
create table tb(工艺序号 int,投入量 int,转换率 int)
insert into tb select 1,20,1
insert into tb select 2, 0,2
insert into tb select 3, 0,1
insert into tb select 4,10,2
insert into tb select 5, 0,3
insert into tb select 6, 5,2
go
;with cte as(
select *,投入量*转换率 as 应产出量 from tb where 工艺序号=1
union all
select b.*,(b.投入量+a.应产出量)*b.转换率 from cte a inner join tb b on a.工艺序号=b.工艺序号-1
)
select * from cte
/*
工艺序号 投入量 转换率 应产出量
----------- ----------- ----------- -----------
1 20 1 20
2 0 2 40
3 0 1 40
4 10 2 100
5 0 3 300
6 5 2 610
(6 行受影响)
*/
go
drop table tb
select
ecm01,ecm03,ecm04,ecm27,sfa06,wip,ecm301,ecm311,ecm312,ecm313,ecm314,tc_ecm04,tc_ecm05,ZhiTL,
MyShouldIn,MyShouldOut
from
(
select
ecm01,ecm03,ecm04,ecm27,nvl((select sum(sfa06) from sfa_file where sfa01=ecm01 and sfa08=ecm04),0)sfa06,
(ecm301*ecm27)-(ecm311+ecm313+ecm314) wip,ecm301,ecm311,ecm312,ecm313,ecm314,tc_ecm04,tc_ecm05,
(select exp(sum(ln(ecm27))) from ecm_file AA where AA.ecm01=BB.ecm01 and AA.ecm03<=BB.ecm03) ZhiTL,
(
select
sum(
nvl((select sum(sfa06) from sfa_file where sfa01=AA.ecm01 and sfa08=AA.ecm04),0)*
nvl((select exp(sum(ln(ecm27))) from ecm_file CC where CC.ecm01=AA.ecm01 and CC.ecm03>=AA.ecm03 AND CC.ecm03<BB.ecm03),1)
)
from ecm_file AA
where AA.ecm01=BB.ecm01 and AA.ecm03<=BB.ecm03
) MyShouldIn, --应投入量,在乘转换率时只要算到上道工序就OK了。但本道的投入量还是要加,故AA.ecm03<=BB.ecm03,而CC.ecm03<BB.ecm03.
(
select
sum(
nvl((select sum(sfa06) from sfa_file where sfa01=AA.ecm01 and sfa08=AA.ecm04),0)*
nvl((select exp(sum(ln(ecm27))) from ecm_file CC where CC.ecm01=AA.ecm01 and CC.ecm03>=AA.ecm03 AND CC.ecm03<=BB.ecm03),1)
)
from ecm_file AA
where AA.ecm01=BB.ecm01 and AA.ecm03<=BB.ecm03
) MyShouldOut --应产出量,在乘转换率时要算到本道工序的。故AA.ecm03<=BB.ecm03,而CC.ecm03<BB.ecm03.
from ecm_file BB
left join tc_ecm_file on ecm01=tc_ecm01 and ecm03=tc_ecm02
where ecm01='S317-110802002' --'S311-110802002'
order by ecm03
)CC