请教------如何展开BOM并找出其中的专用物料?-----急**
各位:
小弟现有一难事,想请各位SQL高手赐教.
有BOM表,记录数以10万计. 现在,需要将最终产品A1/A2/A3展开到最底层, 列出所有"TYPE"字段为 外协/生产/外购件的所有物料及用量.并判这些物料是否只一个最终产品使用. (其中TYPE 对应CHILDID,原本是在另一张表内,为示例方便放在这.)
请问如何才能在MS_SQL SERVER中完成
原表:
PARENTID CHILDID QTY TYPE
A1 b1 1 生产
A1 b2 1 生产
A1 b5 1 外购
A2 b1 1 生产
A2 b4 1 外购
A3 b2 1 生产
A3 b4 1 外购
A3 b1 1 生产
b1 c1 1 虚拟件
b2 c2 1 外协
b2 c3 1 虚拟件
c1 d3 1 外购
c2 d2 1 外购
c2 d1 1 外购
通过SQL得到如下结果:
PARENTID CHILDID QTY TYPE 专用
A1 b1 1 生产 否
A1 b2 1 生产 否
A1 b5 1 外购 是
A1 d3 1 外购 否
A1 c2 1 外协 否
A2 b1 1 生产 否
A2 b4 1 外购 否
A2 d3 1 外购 否
A3 b2 1 生产 否
A3 b4 1 外购 否
A3 b1 1 生产 否
A3 c2 1 外协 否
A3 d2 1 外购 是
表一：品号信息档
品号 品号属性 材料成本 人工成本 本阶人工
MB001 MB025 MB057 MB058 MB061
A M 2
B M 1
C P 2
D P 3
(MB025=M 时只有本阶人工是基础数据，其他的是计算过来的，MB025=P时只有MB057有基础数据）
表二（BOM）
主件品号 元件品号 用量
A B 2（A由2个B组成） B C 1 （B由1个C和3个D组成）
B D 3 计算原理：
1、计算MB025=M 的人工成本，人工成本=本阶人工+下阶人工成本
2、计算材料成本，材料成本=本阶用到材料+下阶材料之和（如本例 结果：
MB001 MB025 MB057 MB058 MB061
A M 22 4 2
B M 11 1 1
C P 2 0 0
D P 3 0 0 11=（3D+1C）=11
22=11*2
4=2B+2
go
create table BOM(主件品号 nvarchar(2),元件品号 nvarchar(2), 用量 int)
insert BOM select 'A','B',2
insert BOM select 'B','C',1
insert BOM select 'B','D',3
go
create table product(MB001 nvarchar(2), MB025 nvarchar(2), MB057 int, MB058 int, MB061 int)
insert product select 'A','M',null,null, 2
insert product select 'B','M',null,null, 3-----改为3测试
insert product select 'C','P', 2 ,null,null
insert product select 'D','P', 3 ,null,null
gogo
create function BomTree(@Product nvarchar(2))
returns numeric(18,5)
as
begindeclare @T table(主件品号 nvarchar(2),元件品号 nvarchar(2), 用量 int,lev int)
declare @i int,@num numeric(18,5)
if not exists(select 1 from BOM where 主件品号=@Product)
begin
select
@num=sum(MB057)
from
product
where
MB001=@Product
return @num
endset @i=0
insert @T select 主件品号,元件品号,用量,@i from BOM where 主件品号=@Product
while @@rowcount>0
begin
set @i=@i+1
insert @T
select
t2.主件品号,t2.元件品号,t.用量*t2.用量,@i
from @t t join BOM t2 on t.元件品号=t2.主件品号
where t.Lev=@i-1
end
select
@num=sum(t.用量*case when t2.MB057>0 then t2.MB057 else 1 end)
from
@t t
join
product t2 on t.元件品号=t2.MB001
where
not exists(select 1 from @t where t.元件品号=主件品号)return @num
end
gocreate function BomTree2(@Product nvarchar(2))
returns numeric(18,5)
as
begindeclare @T table(主件品号 nvarchar(2),元件品号 nvarchar(2), 用量 int,lev int)
declare @i int,@num numeric(18,5)
if not exists(select 1 from BOM t where 主件品号=@Product
and not exists(select 1 from product where MB001=t.元件品号 and isnull(MB061,0)!>0))
begin
select
@num=sum(isnull(MB061,0))
from
product
where
MB001=@Product
return @num
endset @i=0
insert @T select 主件品号,元件品号,用量,@i from BOM where 主件品号=@Product
while @@rowcount>0
begin
set @i=@i+1
insert @T
select
t2.主件品号,t2.元件品号,t.用量*t2.用量,@i
from @t t join BOM t2 on t.元件品号=t2.主件品号
where t.Lev=@i-1
end
select
@num=sum(t.用量*isnull(t2.MB061,1))
from
@t t
join
product t2 on t.元件品号=t2.MB001------改一下判断where
not exists(select 1 from product where MB001=t.元件品号 and isnull(MB061,0)!>0)
return @num
end
goselect
MB001, MB025 , [MB057]=dbo.BomTree(MB001), [MB058]=isnull([MB061],0)+dbo.BomTree2(MB001), [MB061]=isnull([MB061],0)from
product
go
--drop table product,BOM
--drop function BomTree,BomTree2MB001 MB025 MB057 MB058 MB061
----- ----- -------------------- --------------------- -----------
A M 22.00000 8.00000 2
B M 11.00000 6.00000 3
C P 2.00000 .00000 0
D P 3.00000 .00000 0（所影响的行数为 4 行）
INSERT INTO BOM SELECT 'A1','b1',1,'生产'
INSERT INTO BOM SELECT 'A1','b2',1,'生产'
INSERT INTO BOM SELECT 'A1','b5',1,'外购'
INSERT INTO BOM SELECT 'A2','b1',1,'生产'
INSERT INTO BOM SELECT 'A2','b4',1,'外购'
INSERT INTO BOM SELECT 'A3','b2',1,'生产'
INSERT INTO BOM SELECT 'A3','b4',1,'外购'
INSERT INTO BOM SELECT 'A3','b1',1,'生产'
INSERT INTO BOM SELECT 'b1','c1',1,'虚拟件'
INSERT INTO BOM SELECT 'b2','c2',1,'外协'
INSERT INTO BOM SELECT 'b2','c3',1,'虚拟件'
INSERT INTO BOM SELECT 'c1','d3',1,'外购'
INSERT INTO BOM SELECT 'c2','d2',1,'外购'
INSERT INTO BOM SELECT 'c2','d1',1,'外购' GOCREATE FUNCTION F_GETROOT(@PID varchar(2))
RETURNS varchar(2)
AS
BEGIN
DECLARE @ID varchar(2)
WHILE EXISTS(SELECT 1 FROM BOM WHERE CHILDID=@PID)
BEGIN
SET @ID=@PID
SELECT @PID=PARENTID FROM BOM WHERE CHILDID=@ID
END
RETURN @PID
END
GOSELECT PID=DBO.F_GETROOT(PARENTID),CHILDID,qty,type FROM BOM
order by PID, CHILDID
GO/*
PID CHILDID qty type
---- ------- ----------- ----------
A1 b1 1 生产
A1 b2 1 生产
A1 b5 1 外购
A2 b1 1 生产
A2 b4 1 外购
A3 b1 1 生产
A3 b2 1 生产
A3 b4 1 外购
A3 c1 1 虚拟件
A3 c2 1 外协
A3 c3 1 虚拟件
A3 d1 1 外购
A3 d2 1 外购
A3 d3 1 外购（所影响的行数为 14 行）*/
DROP FUNCTION F_GETROOT
DROP TABLE BOM
GO
2、专用----如果CHILDID 只有一个PARENTID，则为专用。