|
他们彼此深信，是瞬间迸发的热情让他们相遇。这样的确定是美丽的，但变幻无常更为美丽 |
|
要求单个item在所有成品中的用量，先参考一个函数
--生成测试数据 create table BOM_1(Item int,bom_head varchar(20),bom_child varchar(20),number int,products_attribute varchar(20)) insert into BOM_1 select 1 ,'A' ,'A1',1,'采购' insert into BOM_1 select 2 ,'A' ,'A2',2,'生产' insert into BOM_1 select 3 ,'A2','A3',3,'生产' insert into BOM_1 select 4 ,'A2','A4',2,'采购' insert into BOM_1 select 5 ,'A3','A5',2,'采购' insert into BOM_1 select 6 ,'A3','A6',1,'采购' insert into BOM_1 select 7 ,'B' ,'B1',1,'采购' insert into BOM_1 select 8 ,'B' ,'B2',2,'生产' insert into BOM_1 select 9 ,'B2','B3',3,'生产' insert into BOM_1 select 10,'B2','B4',2,'采购' insert into BOM_1 select 11,'B3','B5',2,'采购' insert into BOM_1 select 12,'B3','B6',2,'采购' go --创建用户定义函数，用于取每个父节点下子节点的采购配置信息 create function f_stock(@bom_head varchar(20)) returns @t table(bom varchar(20),number int) as begin declare @level int declare @a table(bom varchar(20),number int,products_attribute varchar(20),[level] int) set @level=1 if exists(select 1 from BOM_1 where bom_head=@bom_head) insert into @a select bom_child,number,products_attribute,@level from BOM_1 where bom_head=@bom_head while exists(select 1 from @a where [level]=@level and products_attribute='生产') begin set @level=@level+1 insert into @a(bom,number,products_attribute,[level]) select a.bom_child,a.number,a.products_attribute,@level from BOM_1 a,@a b where a.bom_head=b.bom and b.[level]=@level-1 end insert into @t(bom,number) select bom,number from @a where products_attribute='采购' return end go --执行调用，取父节点'A'一个标准配置分解的采购信息及数量 select * from dbo.f_stock('A') 只不过我的BOM更复杂设计有三个表，item ,bomhead ,bomdetail,希望明天完成，断断续续研究一个星期了 |
