求助邹建老师！！！ 关于BOM的问题 再现等!!!!!!!!!!!!!!
一个产品下可以有多个零件，这些零件还有不同的层次关系。（请问零件还有不同的层次关系是什么意思）
零件属性 说明
产品编号 字符串类型，并且只能是大写字母和数字。最长是10个字符长。
产品名称 字符串类型。最长是50个全角字符的长度。
出厂日期 产品的出厂日期。日期类型。
零件的属性
属性 说明
零件编号 字符串类型，并且只能是大写字母和数字。最长是10个字符长。
零件名称 字符串类型。最长是50个全角字符的长度。
零件数量 零件在BOM中的数量。自然数类型。
零件层次 零件在BOM中的层次。自然数类型。
零件单位 零件的计量单位。为枚举值：个、条、盒。
Productbuf 产品表
字段 长度 类型 说明
ID 8 bigint id
Product_Num 10 varchar` 产品编号 主键
Product_Name 100 varchar 产品名称
Stamp 8 datetime` 时间标志
partsbuf 零件表
字段 长度 类型 说明
ID 8 Bigint 主键
Product_Num 10 varchar` 产品编号
Parts_Num 10 varchar 零件编号
Parts_Name 100 varchar` 零件名称
Parts_Count 4 int 零件数量
Parts_Flag 4 int 零件层次
Parts_Units 4 int 零件单位(0:个、1:条、2盒)
ID 产品编号 零件名称 零件编号 零件数量 零件层次 零件单位
1 a &&&&&&&& a1 3 0 1
2 a &&&&&&&& a2 4 1 1
3 a &&&&&&&& a3 5 1 1
4 a &&&&&&&& a4 6 2 1
bom展开
a
a1
a2
a3
那么a4应该在a3下还是在a2下呢?
如果在加上一个子零件编号那么还用零件层次么? 如果用怎么展开呢?
[解决办法]
CREATE TABLE BOM(PID INT,ID INT)
INSERT INTO BOM SELECT 801,101
INSERT INTO BOM SELECT 801,102
INSERT INTO BOM SELECT 801,103
INSERT INTO BOM SELECT 801,601
INSERT INTO BOM SELECT 601,101
INSERT INTO BOM SELECT 601,105
INSERT INTO BOM SELECT 601,501
INSERT INTO BOM SELECT 501,106
INSERT INTO BOM SELECT 501,121
GO
CREATE FUNCTION F_GETROOT(@PID INT)
RETURNS INT
AS
BEGIN
DECLARE @ID INT
WHILE EXISTS(SELECT 1 FROM BOM WHERE ID=@PID)
BEGIN
SET @ID=@PID
SELECT @PID=PID FROM BOM WHERE ID=@ID
END
RETURN @PID
END
GO
SELECT PID=DBO.F_GETROOT(PID),ID FROM BOM
GO
/*
PID ID
----------- -----------
801 101
801 102
801 103
801 601
801 101
801 105
801 501
801 106
801 121
*/
DROP FUNCTION F_GETROOT
DROP TABLE BOM
GO
--生成测试数据
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')
--生成测试数据
create table BOM(ID INT,PID INT,MSG VARCHAR(1000))
insert into BOM select 1,0,NULL
insert into BOM select 2,1,NULL
insert into BOM select 3,1,NULL
insert into BOM select 4,2,NULL
insert into BOM select 5,3,NULL
insert into BOM select 6,5,NULL
insert into BOM select 7,6,NULL
go
--创建用户定义函数用于取每个父节点下子节点的采购配置信息
create function f_getChild(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)
as
begin
declare @i int
set @i = 1
insert into @t select ID,PID,@i from BOM where PID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.PID,@i
from
BOM a,@t b
where
a.PID=b.ID and b.Level = @i-1
end
return
end
go
--执行查询
select ID from dbo.f_getChild(3)
go
--输出结果
/*
ID
----
5
6
7
*/
--删除测试数据
drop function f_getChild
drop table BOM
[解决办法]
表一：品号信息档
品号 品号属性 材料成本 人工成本 本阶人工
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
go
go
create function BomTree(@Product nvarchar(2))
returns numeric(18,5)
as
begin
declare @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
end
set @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
go
create function BomTree2(@Product nvarchar(2))
returns numeric(18,5)
as
begin
declare @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
end
set @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
go
select
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,BomTree2
MB001 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 行）
[解决办法]
BOMMD结构
MD001 MD003 MD006
主件品号 子件品号 用量
A A1 1
A1 A2 1
A2 B 1
INVMB 料件主档
MB001 MB061
品号 成本单价
A 1
A1 2
A2 3
B 1
结果：
B 1
A2 4
A1 6
A 7
从下往上累算
请给出计算各层BOM的单价的语句
CREATE TABLE BOMMD(MD001 VARCHAR(20),MD003 VARCHAR(20), MD006 INT)
--主件品号 子件品号 用量
INSERT BOMMD SELECT 'A', 'A1', 1
UNION ALL SELECT 'A1' , 'A2' , 1
UNION ALL SELECT 'A2', 'B' , 1
CREATE TABLE INVMB(MB001 VARCHAR(20),MB061 SMALLMONEY)
--品号 成本单价
INSERT INVMB SELECT 'A' ,1
UNION ALL SELECT 'A1' ,2
UNION ALL SELECT 'A2' ,3
UNION ALL SELECT 'B' ,1
GO
CREATE FUNCTION getFEE
(@id VARCHAR(20),@fee SMALLMONEY)
RETURNS SMALLMONEY
AS
BEGIN
DECLARE @pid VARCHAR(20)
SET @pid=@id
SELECT --@id=a.MB001 /*id*/,
@fee=@fee+a.MB061 /*fee*/,
@id=b.MD003 /*sid*/
FROM INVMB a
LEFT JOIN BOMMD b
ON a.MB001=b.MD001
WHERE a.MB001=@pid
IF @id IS NOT NULL
SELECT @fee=dbo.getFEE(@id,@fee)
RETURN @fee
END
GO
SELECT *,dbo.getFEE(MB001,0) 总成本 FROM INVMB
DROP TABLE BOMMD,INVMB
DROP FUNCTION getFEE
[解决办法]
逐级汇总示例(循环逐级累计法)
邹建
CREATE TABLE tb(ID int PRIMARY KEY,PID int,Num int)
INSERT tb SELECT 1,NULL,100
UNION ALL SELECT 2,1 ,200
UNION ALL SELECT 3,2 ,300
UNION ALL SELECT 4,3 ,400
UNION ALL SELECT 5,1 ,500
UNION ALL SELECT 6,NULL,600
UNION ALL SELECT 7,NULL,700
UNION ALL SELECT 8,7 ,800
UNION ALL SELECT 9,7 ,900
GO
--计算的存储过程
CREATE PROC p_Calc
AS
SET NOCOUNT ON
DECLARE @Level int
SET @Level=1
SELECT ID,PID,SUM_Num=Num,
Level=CASE
WHEN EXISTS(SELECT * FROM tb WHERE PID=a.ID)
THEN 0 ELSE 1 END
INTO # FROM tb a
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
UPDATE a SET
Level=@Level,
SUM_Num=ISNULL(a.SUM_Num,0)+ISNULL(b.SUM_Num,0)
FROM # a,(
SELECT aa.PID,SUM_Num=SUM(aa.SUM_Num)
FROM # aa,(SELECT DISTINCT PID FROM # WHERE Level=@Level-1)bb
WHERE aa.PID=bb.PID
AND NOT EXISTS(
SELECT * FROM # WHERE PID=aa.PID AND Level=0)
GROUP BY aa.PID
)b WHERE a.ID=b.PID
END
SELECT a.*,b.SUM_Num
FROM tb a,# b
WHERE a.ID=b.ID
GO
--调用存储过程进行计算
EXEC p_Calc
/*--结果
ID PID Num SUM_Num
---------------- ------------------ ------------------- --------------------
1 NULL 100 1500
2 1 200 900
3 2 300 700
4 3 400 400
5 1 500 500
6 NULL 600 600
7 NULL 700 2400
8 7 800 800
9 7 900 900
--*/
[解决办法]
学习
