BOM配方问题(如何跟据生产数得到所有原料数) 本帖最后由 wxf4155927 于 2009-12-02 17:48:54 编辑 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 参考:--BOM算法--产品配件清单查询示例(邹建)CREATE TABLE Item(ID int,Name varchar(10),Wast decimal(2,2))INSERT Item SELECT 1,N'A产品',0.01UNION ALL SELECT 2,N'B产品',0.02UNION ALL SELECT 3,N'C产品',0.10UNION ALL SELECT 4,N'D配件',0.15UNION ALL SELECT 5,N'E物料',0.03UNION ALL SELECT 6,N'F物料',0.01UNION ALL SELECT 7,N'G配件',0.02CREATE TABLE Bom(ItemID int,ChildId int)INSERT Bom SELECT 1,4UNION ALL SELECT 1,7 --A产品由D配件和G配件组成UNION ALL SELECT 2,1UNION ALL SELECT 2,6UNION ALL SELECT 2,7 --B产品由F物料及G配件组成UNION ALL SELECT 4,5UNION ALL SELECT 4,6 --D配件由F物料组成UNION ALL SELECT 3,2UNION ALL SELECT 3,1 --C产品由A产品和B产品组成GOCREATE FUNCTION f_Bom(@ItemIDs varchar(1000), --要查询物料清单及生产量的产品编号列表(逗号分隔)@Num int --要生产的数量)RETURNS @t TABLE(ItemID int,ChildId int,Nums int,Level int)ASBEGIN DECLARE @Level int SET @Level=1 INSERT @t SELECT a.ItemID,a.ChildId,ROUND(@Num/(1-b.Wast),0),@Level FROM Bom a,Item b WHERE a.ChildId=b.ID AND CHARINDEX(','+RTRIM(a.ItemID)+',',','+@ItemIDs+',')>0 WHILE @@ROWCOUNT>0 and @Level<140 BEGIN SET @Level=@Level+1 INSERT @t SELECT a.ItemID,b.ChildId,ROUND(a.Nums/(1-c.Wast),0),@Level FROM @t a,Bom b,Item c WHERE a.ChildId=b.ItemID AND b.ChildId=c.ID AND a.Level=@Level-1 END RETURNENDGO--调用函数展开产品1、2、3的结构及计算生产10个产品时,各需要多少个配件SELECT a.ItemID,ItemName=b.Name, a.ChildId,ChildName=c.Name, a.Nums,a.LevelFROM f_Bom('1,2,3',10) a,Item b,Item cWHERE a.ItemID=b.ID AND a.ChildId=c.IDORDER BY a.ItemID,a.Level,a.ChildId/*ItemID ItemName ChildId ChildName Nums Level----------- ---------- ----------- ---------- ----------- -----------1 A产品 4 D配件 12 11 A产品 7 G配件 10 11 A产品 5 E物料 12 21 A产品 6 F物料 12 22 B产品 1 A产品 10 12 B产品 6 F物料 10 12 B产品 7 G配件 10 12 B产品 4 D配件 12 22 B产品 7 G配件 10 22 B产品 5 E物料 12 32 B产品 6 F物料 12 33 C产品 1 A产品 10 13 C产品 2 B产品 10 13 C产品 1 A产品 10 23 C产品 4 D配件 12 23 C产品 6 F物料 10 23 C产品 7 G配件 10 23 C产品 7 G配件 10 23 C产品 4 D配件 12 33 C产品 5 E物料 12 33 C产品 6 F物料 12 33 C产品 7 G配件 10 33 C产品 5 E物料 12 43 C产品 6 F物料 12 4(24 row(s) affected)*/drop table itemdrop table bomdrop function f_Bom 这个在ERP中很常见的一般是这样一个物料表(包括成品和原材料)一个BOM表(一个成品对应多个原材料以及用量)那么select 原材料,用量 from bom表 where 成品=产品A 就可以算出单件产品A的原材料和用量了生产多少量的产品A就把这里的用量乘以多少就可以了 谢谢楼上的2位,等会散分dawugui的更贴近我的需求,期待结合我上面的情况给出SQL CODE ,这样更容易理解。因为我对BOM不熟。如果有必要可以加分 BOM的示例挺多的,可以参考大乌龟的。每家情况都不同,修改修改应该差不多。 jf--不想整了:现在看到BOM这三个字就有点怕了...... 呵呵,我对BOM也不熟,所以有劳各位高手了。 级次,递归,循环.BOM表的基本原理,搞清这个原理.再复杂的BOM也能搞出来. 有如下的 BOM 表,parent_part 与 child_part 是多对多的关系,现在要求查询出每个 child_part 的最顶层的 parent_partCREATE TABLE pl_bom( parent_part varchar(10), child_part varchar(10))INSERT INTO pl_bomSELECT 'A','C' UNION ALLSELECT 'B','C' UNION ALLSELECT 'C','D' UNION ALLSELECT 'D','E' UNION ALLSELECT 'D','F' UNION ALLSELECT 'H','F' 即根据上述数据,应该得到如下结果child_part parent_part---------- -----------C AC BD AD BE AE BF AF BF H 处理方法 一般可能会考虑从 child_part 开始扫描的方法。但对于每个 child_part 而言,它有一至多个 parent_part,对于多个parent_part 而言,每个 parent_part 到最顶部的 parent_part 经过的层数还可能不一致,这会导致扫描算法不太好写,而且同一个 parent_part 如果被多个 child_part 引用的话, 还可能导致重复的搜索此 parent_part 的顶 parent_part。下面的算法采用自 parent_part 反推 child_part 的方式,可以避免重复扫描某个 parent_part 到 child_part 的问题-- 使用自顶向下展开-- 因为要删除数据, 所以不能用原始表, 用个临时表SELECT id = IDENTITY(int, 1,1 ), child_part, parent_partINTO #FROM pl_bom -- 从顶往下展DECLARE @Level intSET @Level = 1SELECT id = id * 1, Level = @Level, child_part, parent_partINTO #reFROM # AWHERE NOT EXISTS( SELECT * FROM # WHERE child_part = A.parent_part)WHILE @@ROWCOUNT > 0BEGIN SET @Level = @Level + 1 DELETE A FROM # A, #re B WHERE A.id = B.id AND B.Level = @Level - 1 INSERT #re( id, Level, child_part, parent_part) SELECT A.id, @Level, A.child_part, B.parent_part FROM # A, #re B WHERE A.parent_part = B.child_part AND B.Level = @Level - 1END -- 显示结果SELECT child_part, parent_part FROM #reORDER BY 1, 2 -- 删除临时表DROP TABLE #re, #本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/zjcxc/archive/2007/08/29/1763598.aspx 这是典型的BOM中替代料的计算方法.请查阅BOM与替代料的相关资料来做,如果要想做的简单的话,那就直接将产品和原材料的关系建立到BOM表中,其他中间的半成品就可以不要了.但是这种方法有很大的缺陷,在中间半成品存在的情况下,不能计算出相应的比例。 我现在打算建一简单的BOM表,然后用递归来计算数量有请各位高手贴出更好的方法 日期格式 Ms SQL2000中from 后可以跟 select 的结果吗 数据库的导出问题 误删的数据 如何恢复 在触发器中如何得到一个新插入记录的值? 写SQL select出非交叉的日期 (非常头疼) 菜鸟女请教:在window集成模式下,怎么实现数据的导出啊??????? sql高手请进,排序问题 跨数据库查询的问题,数据库名使用变量 pb数据窗口录入问题 为什么在SQL Server中没有日志文件组的概念?只有数据文件组. 求高人解答
--产品配件清单查询示例(邹建)
CREATE TABLE Item(ID int,Name varchar(10),Wast decimal(2,2))
INSERT Item SELECT 1,N'A产品',0.01
UNION ALL SELECT 2,N'B产品',0.02
UNION ALL SELECT 3,N'C产品',0.10
UNION ALL SELECT 4,N'D配件',0.15
UNION ALL SELECT 5,N'E物料',0.03
UNION ALL SELECT 6,N'F物料',0.01
UNION ALL SELECT 7,N'G配件',0.02CREATE TABLE Bom(ItemID int,ChildId int)
INSERT Bom SELECT 1,4
UNION ALL SELECT 1,7 --A产品由D配件和G配件组成
UNION ALL SELECT 2,1
UNION ALL SELECT 2,6
UNION ALL SELECT 2,7 --B产品由F物料及G配件组成
UNION ALL SELECT 4,5
UNION ALL SELECT 4,6 --D配件由F物料组成
UNION ALL SELECT 3,2
UNION ALL SELECT 3,1 --C产品由A产品和B产品组成
GOCREATE FUNCTION f_Bom(
@ItemIDs varchar(1000), --要查询物料清单及生产量的产品编号列表(逗号分隔)
@Num int --要生产的数量
)RETURNS @t TABLE(ItemID int,ChildId int,Nums int,Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t SELECT a.ItemID,a.ChildId,ROUND(@Num/(1-b.Wast),0),@Level
FROM Bom a,Item b
WHERE a.ChildId=b.ID
AND CHARINDEX(','+RTRIM(a.ItemID)+',',','+@ItemIDs+',')>0
WHILE @@ROWCOUNT>0 and @Level<140
BEGIN
SET @Level=@Level+1
INSERT @t SELECT a.ItemID,b.ChildId,ROUND(a.Nums/(1-c.Wast),0),@Level
FROM @t a,Bom b,Item c
WHERE a.ChildId=b.ItemID
AND b.ChildId=c.ID
AND a.Level=@Level-1
END
RETURN
END
GO--调用函数展开产品1、2、3的结构及计算生产10个产品时,各需要多少个配件
SELECT a.ItemID,ItemName=b.Name,
a.ChildId,ChildName=c.Name,
a.Nums,a.Level
FROM f_Bom('1,2,3',10) a,Item b,Item c
WHERE a.ItemID=b.ID
AND a.ChildId=c.ID
ORDER BY a.ItemID,a.Level,a.ChildId/*
ItemID ItemName ChildId ChildName Nums Level
----------- ---------- ----------- ---------- ----------- -----------
1 A产品 4 D配件 12 1
1 A产品 7 G配件 10 1
1 A产品 5 E物料 12 2
1 A产品 6 F物料 12 2
2 B产品 1 A产品 10 1
2 B产品 6 F物料 10 1
2 B产品 7 G配件 10 1
2 B产品 4 D配件 12 2
2 B产品 7 G配件 10 2
2 B产品 5 E物料 12 3
2 B产品 6 F物料 12 3
3 C产品 1 A产品 10 1
3 C产品 2 B产品 10 1
3 C产品 1 A产品 10 2
3 C产品 4 D配件 12 2
3 C产品 6 F物料 10 2
3 C产品 7 G配件 10 2
3 C产品 7 G配件 10 2
3 C产品 4 D配件 12 3
3 C产品 5 E物料 12 3
3 C产品 6 F物料 12 3
3 C产品 7 G配件 10 3
3 C产品 5 E物料 12 4
3 C产品 6 F物料 12 4(24 row(s) affected)
*/
drop table item
drop table bom
drop function f_Bom
一般是这样
一个物料表(包括成品和原材料)
一个BOM表(一个成品对应多个原材料以及用量)
那么select 原材料,用量 from bom表 where 成品=产品A 就可以算出单件产品A的原材料和用量了
生产多少量的产品A就把这里的用量乘以多少就可以了
dawugui的更贴近我的需求,期待结合我上面的情况给出SQL CODE ,这样更容易理解。因为我对BOM不熟。如果有必要可以加分
BOM表的基本原理,搞清这个原理.再复杂的BOM也能搞出来.
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/zjcxc/archive/2007/08/29/1763598.aspx
如果要想做的简单的话,那就直接将产品和原材料的关系建立到BOM表中,
其他中间的半成品就可以不要了.但是这种方法有很大的缺陷,
在中间半成品存在的情况下,不能计算出相应的比例。