哦,存储过程中
delete from pual_bom_temp_qty
这句请删除掉。
正确的结果应该是
A = 10 + 3/(3+2)*40 + 4/(4+5) * 20 * 3/(3+2)= 39//约等于
B = 30
c = 5 + 5/(5+4) * 20 = 16
d = 2/(3+2) * 40 + 4/(4+5) * 20 * 2/(3+2)= 20 //约等于
delete from pual_bom_temp_qty
这句请删除掉。
正确的结果应该是
A = 10 + 3/(3+2)*40 + 4/(4+5) * 20 * 3/(3+2)= 39//约等于
B = 30
c = 5 + 5/(5+4) * 20 = 16
d = 2/(3+2) * 40 + 4/(4+5) * 20 * 2/(3+2)= 20 //约等于
select part_no,part_name,(qty/@LeafQtySum)*@NodeQty
这里没有考虑到父项的数量,所以计算不正确啊
数据和要求的结果以及正确的公式上面都有了,就是不知道该怎么写
super temp 00001 a 10.00
super temp 00002 b 30.00
super temp 00006 leaf2 20.00
super temp 00003 leaf 40.00
super temp 00004 c 5.00
super 00006 00004 c 5.00
super 00006 00003 leaf 4.00
super 00003 00001 a 3.00
super 00003 00005 d 2.00
忽略USERNAME,
展开BOM_NO字段值为TEMP的树,应该为
|-00001 a 10
|-00002 b 30
|-00003 leaf 40
|-00001 a 3
|-00005 d 2
|-00004 c 5
|-00006 leaf2 20
|-00004 c 5
|-00003 leaf 4
|-00001 a 3
|-00005 d 2
其中leaf由a和b以3:2的比例构成,leaf2由c和leaf以5:4构成
如第一层的leaf的分量为40,那么构成leaf的a=A占LEAF的比例乘以leaf在本层的分量,即a=3/(3+2)*40 = 24,d=2/(3+2)*40 = 24.
如第二层的leaf的分量为4,则第二层的leaf的实际分量为4/(4+5)*20,
那么a的分量应为3/(3+2) * 4/(4+5)*20
其他同理
最后的要求的结果为
A = 10 + 3/(3+2)*40 + 4/(4+5) * 20 * 3/(3+2)= 39//约等于
B = 30
c = 5 + 5/(5+4) * 20 = 16
d = 2/(3+2) * 40 + 4/(4+5) * 20 * 2/(3+2)= 20 //约等于
求算法及存储过程
isleaf=1代表有子项,isleaf = 0代表为最终级材料,要求计算所有isleaf = 0的材料的实际分量
分解到每一个最终材料的总数=所有bom_no = 'temp'的qty总数
CREATE TABLE [pual_bom_temp_input] (
[username] [varchar] (30) NOT NULL ,
[bom_no] [varchar] (5) NOT NULL ,
[part_no] [varchar] (30) NOT NULL ,
[part_name] [varchar] (30) NOT NULL ,
[qty] [smallmoney] NOT NULL ,
[isleaf] [int] NOT NULL ,
[isprint] [int] NOT NULL )
-- ALTER TABLE [pual_bom_temp_input] WITH NOCHECK ADD CONSTRAINT [DF_pual_bom_temp_input_qty] DEFAULT (0) FOR [qty],CONSTRAINT [DF_pual_bom_temp_input_isleaf] DEFAULT (0) FOR [isleaf],CONSTRAINT [DF_pual_bom_temp_input_isprint] DEFAULT (0) FOR [isprint],CONSTRAINT [PK_pual_bom_temp_input] PRIMARY KEY NONCLUSTERED ( [username] )
-- CREATE UNIQUE INDEX [PK_pual_bom_temp_input] ON [pual_bom_temp_input] ( bom_no ) INSERT [pual_bom_temp_input] ( [username] , [bom_no] , [part_no] , [part_name] , [qty] , [isleaf] , [isprint] ) VALUES ( 'super' , 'temp' , '00001 ' , 'a' , 10.00 , 0 , 0 )
INSERT [pual_bom_temp_input] ( [username] , [bom_no] , [part_no] , [part_name] , [qty] , [isleaf] , [isprint] ) VALUES ( 'super' , 'temp' , '00002' , 'b' , 30.00 , 0 , 0 )
INSERT [pual_bom_temp_input] ( [username] , [bom_no] , [part_no] , [part_name] , [qty] , [isleaf] , [isprint] ) VALUES ( 'super' , 'temp' , '00006' , 'leaf2' , 20.00 , 1 , 0 )
INSERT [pual_bom_temp_input] ( [username] , [bom_no] , [part_no] , [part_name] , [qty] , [isleaf] , [isprint] ) VALUES ( 'super' , '00006' , '00004' , 'c' , 5.00 , 0 , 0 )
INSERT [pual_bom_temp_input] ( [username] , [bom_no] , [part_no] , [part_name] , [qty] , [isleaf] , [isprint] ) VALUES ( 'super' , 'temp ' , '00003' , 'leaf ' , 40.00 , 1 , 0 )
INSERT [pual_bom_temp_input] ( [username] , [bom_no] , [part_no] , [part_name] , [qty] , [isleaf] , [isprint] ) VALUES ( 'super' , 'temp ' , '00004' , 'c' , 5.00 , 0 , 0 )
INSERT [pual_bom_temp_input] ( [username] , [bom_no] , [part_no] , [part_name] , [qty] , [isleaf] , [isprint] ) VALUES ( 'super' , '00003' , '00001' , 'a' , 3.00 , 0 , 0 )
INSERT [pual_bom_temp_input] ( [username] , [bom_no] , [part_no] , [part_name] , [qty] , [isleaf] , [isprint] ) VALUES ( 'super' , '00003' , '00005' , 'd' , 2.00 , 0 , 0 )
INSERT [pual_bom_temp_input] ( [username] , [bom_no] , [part_no] , [part_name] , [qty] , [isleaf] , [isprint] ) VALUES ( 'super' , '00006' , '00003' , 'leaf' , 4.00 , 1 , 0 )
goCREATE PROCEDURE pual_compute_bom
@username char(30),
@bom_no char(5)
AS
set nocount on
CREATE TABLE #([part_no] [varchar] (30),[qty] smallmoney,[part_name] [varchar] (30),level int)
declare @l int
set @l=0
INSERT # select [part_no],qty,part_name,@l
from pual_bom_temp_input
where username=@username
and bom_no=@bom_no
while @@rowcount>0
begin
set @l=@l+1
insert # select a.[part_no],
a.qty/
(select sum(qty) from pual_bom_temp_input where bom_no=b.part_no)
*b.qty,
a.part_name,@l
from pual_bom_temp_input a,# b
where b.level=@l-1
and a.bom_no=b.part_no
end
select part_no,part_name,qty=sum(qty)
from # a
where not exists(
select * from pual_bom_temp_input
where bom_no=a.part_no)
group by part_no,part_namego--调用
exec pual_compute_bom 'super','temp'
go
drop table pual_bom_temp_input
drop proc pual_compute_bom/*--结果part_no part_name qty
----------- ----------- --------
00001 a 39.3328
00002 b 30.0000
00004 c 16.1100
00005 d 19.5552
--*/