上次只发了查单个BOM的报表，这次提供全阶批量导BOM的

天晴

上次只发了查单个BOM的报表，这次提供全阶批量导BOM的。这里只对自制件展开，另外如需其他列名，请自行修改增加。
with my1 as(select * from (select t2.InvCode fi,t5.cinvname,t5.cinvstd,t4.InvCode bi,t6.cinvstd bc,t6.cinvname bn,cast(t3.BaseQtyN/t3.BaseQtyD as float) us,case when t6.bPurchase=1 then t6.iInvNCost else 0 end iInvNCost,t6.bSelf from bom_parent t1 inner join bas_part t2 on t1.ParentId=t2.PartId
inner join bom_opcomponent t3 on t3.BomId=t1.BomId
inner join bas_part t4 on t3.ComponentId=t4.PartId
inner join Inventory t5 on t5.cInvCode=t2.InvCode
inner join Inventory t6 on t6.cInvCode=t4.InvCode
where
t4.InvCode not in('1196001','1196002','1196003')) s
union all
select my1.fi,my1.cinvname,my1.cinvstd,aaa.bi,aaa.bc,aaa.bn,my1.us*aaa.us us,aaa.iInvNCost,aaa.bSelf from my1,(select t2.InvCode fi,t4.InvCode bi,case when t6.bPurchase=1 then t6.iInvNCost else 0 end iInvNCost,t6.cinvstd bc,t6.cinvname bn,cast(t3.BaseQtyN/t3.BaseQtyD as float) us,t6.bSelf from bom_parent t1 inner join bas_part t2 on t1.ParentId=t2.PartId
inner join bom_opcomponent t3 on t3.BomId=t1.BomId
inner join bas_part t4 on t3.ComponentId=t4.PartId
inner join Inventory t6 on t6.cInvCode=t4.InvCode  
where t4.InvCode not in('1196001','1196002','1196003')) aaa where my1.bi = aaa.fi and my1.bSelf=1)

select * from my1

lyo3

应该加一些说明文字