22,209
社区成员
发帖
与我相关
我的任务
分享
with cte_1
as
(select A.*,B.数量 as 库存,100-B.数量 as 欠料
from
(select top 1 母件编码,母件编码 as 子件编码,1 as 基本用量,cpath,100 as 需求数量 from TMP_BOM order by cpath) as A
join tmp_kc B on A.母件编码=B.物料编码),
cte_2
as
(select B.*,A.欠料 as 需求数量,isnull(C.数量,0) as 库存,A.欠料-isnull(C.数量,0) as 欠料
from cte_1 as A
join TMP_BOM as B on A.母件编码=B.母件编码
left join tmp_kc C on B.子件编码=C.物料编码
where charindex('-',B.cpath)=0),
cte_3
as
(select * from cte_2
union all
select B.母件编码, B.子件编码, B.基本用量, B.cpath,A.欠料*B.基本用量,B.数量,A.欠料*B.基本用量-B.数量
from cte_2 as A
join
(select A.*,B.数量 from TMP_BOM A
left join tmp_kc B on A.子件编码=B.物料编码
where charindex('-',A.cpath)<>0) as B
on B.cpath like A.cpath+'-%')
select * from cte_1
union all
select * from cte_3