200分再向高手请教BOM的单位用量计算问题
表结构:
表名ps_mstr
字段:ps_par(父料号)
ps_comp（子料号）
ps_pty_per（单位用量，即一个父料号使用的单位数量，比如ps_qty_per是2.8的话就相当于要做1个ps_par需要用2.8个的ps_comp） .
业务说明：
是产品结构表，比如说成品是FG001，
下面有半成品SFG001,SFG002,SFG003
半成品SFG001下面有中间件WIP001，WIP002;SFG002下有WIP003,WIP004,WIP005;SFG003下面有WIP006，
最后WIP001下面有原材料RAW001,RAW002,WIP002下面有原材料RAW003,WIP003下面有原材料RAW004,RAW005等等。
FG001
|
---------------------------------
| | |
SFG001 SFG002 SFG003
| |
------------------- ---------------------
| | | | |
WIP001 WIP002 WIP003 WIP004 WIP005 WIP006
| | |
RAW001 RAW002 RAW003 RAW004,RAW005 ............
当然这只是一个产品结构，这个粒子只有4层，实际层次可能更多，最多的10多层。
某个半成品或者原材料可能被多个成品用到。
举例数据：
ps_par ps_comp ps_qty_per
FG001 SFG001 1
FG001 SFG002 1
FG001 SFG003 1
SFG001 WIP001 2
SFG001 WIP002 2
SFG002 WIP003 3
SFG002 WIP004 3
SFG002 WIP005 2
SFG003 WIP006 3
WIP001 RAW001 2.66
WIP001 RAW002 2.33
WIP002 RAW003 3.21
WIP003 RAW004 1.89
WIP003 RAW005 1.86
现在需要计算由成品到指定原材料的单位用量，即比如需要计算到成品FG001需要使用多少的RAW001,RAW002,RAW003?
[解决办法]
--生成测试数据
create table BOM(ID INT,PID INT,NUM INT)
insert into BOM select 1,0,1
insert into BOM select 2,1,2
insert into BOM select 3,1,3
insert into BOM select 4,2,2
insert into BOM select 5,3,1
insert into BOM select 6,5,2
insert into BOM select 7,6,1
go
--创建用户定义函数
create function f_getChild(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),PID VARCHAR(10),NUM INT,Level INT)
as
begin
declare @i int,@ret varchar(8000)
set @i = 1
insert into @t select ID,PID,NUM,@i from BOM where PID = @ID
while @@rowcount <> 0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.PID,a.NUM*B.NUM,@i
from
BOM a,@t b
where
a.PID=b.ID and b.Level = @i-1
end
delete t from @t t where exists(select 1 from @t where PID=t.ID)
return
end
go
--执行查询
select ID,NUM from dbo.f_getChild(1)
go
--输出结果
/*
ID NUM
---------- -----------
4 4
7 6
*/
--删除测试数据
drop function f_getChild
drop table BOM
[解决办法]
這個是由上往下展,套用之前帖子里的那2個function中的另一個(查找所有子結點的),差不多就可以解決了
[解决办法]
CREATE function g_bom(@p varchar(8))
returns @tt table(ps_comp varchar(8),qty numeric(9,2))
as
begin
declare @id int,@f varchar(8),@m numeric(9,2)
declare @t table(id int identity(1,1),ps_par varchar(10),ps_comp varchar(10),qty numeric(9,2))
insert into @t select ps_par,ps_comp,qty from ps_mstr where ps_par=@p
while @@rowcount> 0
begin
select top 1 @id=id,@f=ps_comp,@m=qty from @t a where exists(select * from ps_mstr where ps_par=a.ps_comp)
insert into @t select ps_par,ps_comp,qty*@m from ps_mstr where ps_par=@f
delete from @t where id=@id
end
insert into @tt select ps_comp,sum(qty) from @t group by ps_comp
return
end
select * from g_bom( 'FG001 ')
[解决办法]
展BOM的东东
我N年前的一个贴子，有兴趣可以去看一下，可以实现树型目录的大部份功能，两个自定义函数
http://topic.csdn.net/t/20030120/12/1375432.html
可以实现：
得到ID为X的所有下层类别ID串
查询ID为X的所有下层记录
得到ID为X顶层ID
得到ID为X所在枝的所有ID
得到ID为X到顶层的ID串
按照树顺序排序
[解决办法]
实现此功能,建议不要使用一个表来实现
简单的话可以使用两个表来实现:
1.产品/材料表
此表包括产品/半成品/材料....的基础信息
材料编码,材料名称,单位,...........
2.产品结构表(BOM)
此表中的BOM编码是组成方式为:01 0101 010101....,以此类推,这样可以达到有限级的要求(只要将BOM编码的设为一定长度.此处为每级占两位,则10级共占20位)
BOM编码,材料编码,级数......
3.在执行查询和统计时,由于产品结构的关系由BOM编码直接关联,所以统计/查询时会方便很多
例如:
BOM编码 材料编码 级数 数量
---------------------------------
01 FG001 0
0101 SFG001 1
0102 SFG002 1
010101 WIP001 2
010102 WIP002 2
01010101 RAW001 3 2.66
01010101 RAW002 3 2.33
01010201 RAW001 3 2.66
01010201 RAW002 3 2.33
此时查询统计时可以如此操作:
1)查询统计产品FG001的使用材料数
Select 材料编码,Sum(数量) From BOM表 WHere Bom编码 like '01% '
这样将统计出所有结构中的产品或半产品或材料的数量,如果仅统计材料的数量,可以在材料表中为材料设置标记,以区分是产品还是半产品或是材料
这里的语句只是一个简单的说明,具体执行时可能会复杂一些
[解决办法]
set nocount on
declare @ps_mstr table (ps_par varchar(10),ps_comp varchar(10),qty int)
insert into @ps_mstr
select 'FG001 ', 'SFG001 ', 3 union all
select 'FG001 ' , 'SFG002 ', 4 union all
select 'FG001 ' , 'SFG003 ', 5 union all
select 'SFG001 ', 'WIP001 ', 6 union all
select 'SFG001 ' , 'WIP002 ', 2 union all
select 'SFG002 ' , 'WIP001 ', 3 union all
select 'SFG002 ' , 'WIP002 ', 4 union all
select 'SFG002 ' , 'WIP003 ', 6 union all
select 'SFG003 ' , 'WIP001 ', 7 union all
select 'WIP001 ' , 'RAW001 ', 8 union all
select 'WIP001 ' , 'RAW002 ' , 9 union all
select 'WIP002 ' , 'RAW003 ' , 6 union all
select 'WIP003 ' , 'RAW004 ' , 7 union all
select 'WIP003 ' , 'RAW001 ' , 8
declare @par varchar(50)
set @par= 'fg001 '
select @par ps_par;
with cte_c (ps_par,ps_comp,qty,israw) as (
select ps_par,ps_comp,qty,(case
when exists(select * from @ps_mstr where ps_par=a.ps_comp) then 1 else 0 end) isRaw
from @ps_mstr a
where ps_par=@par
union all
select a.ps_par,a.ps_comp,a.qty*c.qty qty,(case
when exists(select * from @ps_mstr where ps_par=a.ps_comp) then 1 else 0 end) isRaw
from @ps_mstr a
join cte_c c on a.ps_par=c.ps_comp)
select ps_comp,sum(qty) qty from cte_c where israw=0 group by ps_comp
set @par= 'sfg001 '
select @par ps_par;
with cte_c (ps_par,ps_comp,qty,israw) as (
select ps_par,ps_comp,qty,(case
when exists(select * from @ps_mstr where ps_par=a.ps_comp) then 1 else 0 end) isRaw
from @ps_mstr a
where ps_par=@par
union all
select a.ps_par,a.ps_comp,a.qty*c.qty qty,(case
when exists(select * from @ps_mstr where ps_par=a.ps_comp) then 1 else 0 end) isRaw
from @ps_mstr a
join cte_c c on a.ps_par=c.ps_comp)
select ps_comp,sum(qty) qty from cte_c where israw=0 group by ps_comp
set @par= 'sfg002 '
select @par ps_par;
with cte_c (ps_par,ps_comp,qty,israw) as (
select ps_par,ps_comp,qty,(case
when exists(select * from @ps_mstr where ps_par=a.ps_comp) then 1 else 0 end) isRaw
from @ps_mstr a
where ps_par=@par
union all
select a.ps_par,a.ps_comp,a.qty*c.qty qty,(case
when exists(select * from @ps_mstr where ps_par=a.ps_comp) then 1 else 0 end) isRaw
from @ps_mstr a
join cte_c c on a.ps_par=c.ps_comp)
select ps_comp,sum(qty) qty from cte_c where israw=0 group by ps_comp
set @par= 'sfg003 '
select @par ps_par;
with cte_c (ps_par,ps_comp,qty,israw) as (
select ps_par,ps_comp,qty,(case
when exists(select * from @ps_mstr where ps_par=a.ps_comp) then 1 else 0 end) isRaw
from @ps_mstr a
where ps_par=@par
union all
select a.ps_par,a.ps_comp,a.qty*c.qty qty,(case
when exists(select * from @ps_mstr where ps_par=a.ps_comp) then 1 else 0 end) isRaw
from @ps_mstr a
join cte_c c on a.ps_par=c.ps_comp)
select ps_comp,sum(qty) qty from cte_c where israw=0 group by ps_comp
set @par= 'wip001 '
select @par ps_par;
with cte_c (ps_par,ps_comp,qty,israw) as (
select ps_par,ps_comp,qty,(case
when exists(select * from @ps_mstr where ps_par=a.ps_comp) then 1 else 0 end) isRaw
from @ps_mstr a
where ps_par=@par
union all
select a.ps_par,a.ps_comp,a.qty*c.qty qty,(case
when exists(select * from @ps_mstr where ps_par=a.ps_comp) then 1 else 0 end) isRaw
from @ps_mstr a
join cte_c c on a.ps_par=c.ps_comp)
select ps_comp,sum(qty) qty from cte_c where israw=0 group by ps_comp
set @par= 'wip002 '
select @par ps_par;
with cte_c (ps_par,ps_comp,qty,israw) as (
select ps_par,ps_comp,qty,(case
when exists(select * from @ps_mstr where ps_par=a.ps_comp) then 1 else 0 end) isRaw
from @ps_mstr a
where ps_par=@par
union all
select a.ps_par,a.ps_comp,a.qty*c.qty qty,(case
when exists(select * from @ps_mstr where ps_par=a.ps_comp) then 1 else 0 end) isRaw
from @ps_mstr a
join cte_c c on a.ps_par=c.ps_comp)
select ps_comp,sum(qty) qty from cte_c where israw=0 group by ps_comp
set @par= 'wip003 '
select @par ps_par;
with cte_c (ps_par,ps_comp,qty,israw) as (
select ps_par,ps_comp,qty,(case
when exists(select * from @ps_mstr where ps_par=a.ps_comp) then 1 else 0 end) isRaw
from @ps_mstr a
where ps_par=@par
union all
select a.ps_par,a.ps_comp,a.qty*c.qty qty,(case
when exists(select * from @ps_mstr where ps_par=a.ps_comp) then 1 else 0 end) isRaw
from @ps_mstr a
join cte_c c on a.ps_par=c.ps_comp)
select ps_comp,sum(qty) qty from cte_c where israw=0 group by ps_comp
/*
ps_par
--------------------------------------------------
fg001
ps_comp qty
---------- -----------
RAW001 712
RAW002 585
RAW003 132
RAW004 168
ps_par
--------------------------------------------------
sfg001
ps_comp qty
---------- -----------
RAW001 48
RAW002 54
RAW003 12
ps_par
--------------------------------------------------
sfg002
ps_comp qty
---------- -----------
RAW001 72
RAW002 27
RAW003 24
RAW004 42
ps_par
--------------------------------------------------
sfg003
ps_comp qty
---------- -----------
RAW001 56
RAW002 63
ps_par
--------------------------------------------------
wip001
ps_comp qty
---------- -----------
RAW001 8
RAW002 9
ps_par
--------------------------------------------------
wip002
ps_comp qty
---------- -----------
RAW003 6
ps_par
--------------------------------------------------
wip003
ps_comp qty
---------- -----------
RAW001 8
RAW004 7
*/
