Bom中的实体物料与用量

       在制造业BOM中存在一种“虚拟件”作为供应类型的应用。该部分应用主要用于实际不用于实际出入库的物料，在BOM中为了体现结构或其它应用的需要而设置的一种供应类型。

如下图BOM：

根据以上的情况，在实际生产订单或现场实体物料使用的BOM结构如下：

BOM的设计结构如下：

表结构为：

表名：	Bom
字段名称	字段属性	说明
P	Varchar(max)	父项
C	Varchar(max)	子项
Wt	Int	供应类型
Qty	int	用量

创建BOM

if OBJECT_ID('Bom') is not null

begin

    drop tableBom

end

create table Bom

(

p varchar(max),

c varchar(max),

wt int,

qty float

)

insert into bom(p,c,wt,qty)values('A','A1',4,2)

insert into bom(p,c,wt,qty)values('A','A2',4,3)

insert into bom(p,c,wt,qty)values('A','A3',3,1)

insert into bom(p,c,wt,qty)values('A1','A11',3,2)

insert into bom(p,c,wt,qty)values('A1','A12',3,3)

insert into bom(p,c,wt,qty)values('A1','A13',3,1)

insert into bom(p,c,wt,qty)values('A2','A21',4,2)

insert into bom(p,c,wt,qty)values('A2','A22',3,3)

insert into bom(p,c,wt,qty)values('A2','A23',3,1)

insert into bom(p,c,wt,qty)values('A21','A211',3,2)

insert into bom(p,c,wt,qty)values('A21','A212',3,4)

表

拟算角本

IF OBJECT_ID('TEMPDB..#BOM') IS NOT NULL

BEGIN

    DROP TABLE#BOM

END

SELECT P,C,wt,qty,0 AS L INTO #BOM FROM BOM WHERE 1=0 AND  P='A'

INSERT INTO #BOM SELECT P,C,WT,QTY ,1 AS L FROM BOM WHERE P='A'

DECLARE @ID INT

SELECT @ID=COUNT( DISTINCT WT) FROM #BOM WHERE L IN(SELECT MAX(L) AS L FROM #BOM ) AND wt=4 

WHILE @ID=1

BEGIN

  INSERT INTO #BOM SELECT DISTINCT A.p,B.c,B.wt,B.qty*A.qty AS QTY,A.L+1 AS L FROM #BOM A INNER JOIN Bom B ON A.c=B.p INNER JOIN (SELECT * FROM(SELECT ROW_NUMBER()OVER(PARTITION BY C,WT,L ORDER BY C,WT,L DESC) AS ID,P,C,WT,L,QTY FROM #BOM WHERE wt=4)X WHERE ID=1)A1 ON A1.C=A1.C AND A.L=A1.L AND A.wt=A1.wt WHERE A.L IN(SELECT MAX(L) FROM #BOM)

SELECT @ID=COUNT( DISTINCT WT) FROM #BOM WHERE L IN(SELECT MAX(L) AS L FROM #BOM ) AND wt=4

END

SELECT  * FROM #BOM WHERE WT=3 ORDER BY P,L

结果