一段展开BOM尾阶用料清单与用量合计的算法。

一段展开BOM尾阶用料清单与用量合计的算法。

Create Table #tmp(SubCode Nvarchar(50),ParentCode Nvarchar(50),Qty Numeric(18,6))

Insert Into #tmp(SubCode,ParentCode,Qty)
select 'S1','P1',1
Insert Into #tmp(SubCode,ParentCode,Qty)
select 'S2','P1',2
Insert Into #tmp(SubCode,ParentCode,Qty)
select 'SU1','S1',1
Insert Into #tmp(SubCode,ParentCode,Qty)
select 'SU2','S1',1
Insert Into #tmp(SubCode,ParentCode,Qty)
select 'SU1','S2',1
Insert Into #tmp(SubCode,ParentCode,Qty)
select 'SU2','S2',2

Insert Into #tmp(SubCode,ParentCode,Qty)
select 'S1','P2',1
Insert Into #tmp(SubCode,ParentCode,Qty)
select 'S2','P2',2

Create Table #tmp2(HParentCode Nvarchar(50),ParentCode Nvarchar(50),SubCode NVarchar(50),
Qty Numeric(18,6),LevelNum int,AutoID Int IDENTITY(1,1))
Insert Into #tmp2(HParentCode,ParentCode,SubCode,Qty,LevelNum)
Select distinct ParentCode,ParentCode,ParentCode,1,0
From #tmp

Declare @LevelNum int
Set @LevelNum=0
While Exists(select 1 from #tmp2 A Inner join #tmp B on A.SubCode=B.ParentCode   Where LevelNum=@LevelNum)
Begin
  SET @LevelNum=@LevelNum+1
  Insert Into #tmp2(HParentCode,ParentCode,SubCode,Qty,LevelNum)
  Select A.HParentCode,A.SubCode,B.SubCode,A.Qty*B.Qty,@LevelNum
  from #tmp2 A Inner join #tmp B on A.SubCode=B.ParentCode  Where LevelNum=@LevelNum-1

  --展到100阶时即退出

  If @LevelNum>=101
    Break
End

Delete #tmp2 where SubCode in (select ParentCode From #tmp2)
Delete #tmp2 where HParentCode in (select ParentCode From #tmp2)

Select HParentCode,SubCode,Sum(Qty) as Qty From #tmp2 Group By HParentCode,SubCode Order By HParentCode,SubCode

/*
drop table #tmp2
drop table #tmp
*/