高度难动态BOM成本计算
成本计算成最下级算到最上级的。 BOM表
产品 产品数量 原料 原料数 单价
BILLID MGOODSID MQTY DGOODSID ITEMNO DQTY DPRICE
584 A001 1 B001 1 1 ?? (半成品)
584 A001 1 C001 2 2 5 (原料) 2个C001组成
584 A001 1 D001 3 1 5 (原料)
588 B001 1 E001 1 1 3 (原料)
588 B001 1 F001 2 2 ?? (半成品) 2个F001组成
560 F001 1 G001 1 1 2 (原料)
560 F001 1 J001 2 2 2 (原料) 2个J001组成 树型结构如下:
A001 *1
| | |
B001 *1 C001 *2 D001 *1
| |
E001 *1 F001 *2
| |
G001 *1 J001 *2
求:
1、要求结果按物料清单最底层原料起计算推算出半成品、成品的单价,希望能做成函数或存储过程。 MGOODSID MQTY, DGOODSID, DQTY, DPRICE, DAM
A001 1 B001 1 15 15 ---B001单价是算出来的
A001 1 C001 2 5 10
A001 1 D001 1 5 5
B001 1 E001 1 3 3
B001 1 F001 2 6 12 ---F001单价是算出来的
F001 1 G001 1 2 2
F001 1 J001 2 2 4 2、如果BOM数据量比较大时上面的方法运算就比较慢了，有没有办法只查询某货品如 A001时,
只计算跟A001相关货品。
WHERE M.GOODSID='A001'时的结果如下：
MGOODSID MQTY, DGOODSID, DQTY, DPRICE, DAM
A001 1 B001 1 15 15 ---B001单价是算出来的
A001 1 C001 2 5 10
A001 1 D001 1 5 5
成本计算成最下级算到最上级的。 BOM表
产品 产品数量 原料 原料数 单价
BILLID MGOODSID MQTY DGOODSID ITEMNO DQTY DPRICE
584 A001 1 B001 1 1 ?? (半成品)
584 A001 1 C001 2 2 5 (原料) 2个C001组成
584 A001 1 D001 3 1 5 (原料)
588 B001 1 E001 1 1 3 (原料)
588 B001 1 F001 2 2 ?? (半成品) 2个F001组成
560 F001 1 G001 1 1 2 (原料)
560 F001 1 J001 2 2 2 (原料) 2个J001组成 树型结构如下:
A001 *1
| | |
B001 *1 C001 *2 D001 *1
| |
E001 *1 F001 *2
| |
G001 *1 J001 *2
求:
1、要求结果按物料清单最底层原料起计算推算出半成品、成品的单价,希望能做成函数或存储过程。 MGOODSID MQTY, DGOODSID, DQTY, DPRICE, DAM
A001 1 B001 1 15 15 ---B001单价是算出来的
A001 1 C001 2 5 10
A001 1 D001 1 5 5
B001 1 E001 1 3 3
B001 1 F001 2 6 12 ---F001单价是算出来的
F001 1 G001 1 2 2
F001 1 J001 2 2 4 2、如果BOM数据量比较大时上面的方法运算就比较慢了，有没有办法只查询某货品如 A001时,
只计算跟A001相关货品。
WHERE M.GOODSID='A001'时的结果如下：
MGOODSID MQTY, DGOODSID, DQTY, DPRICE, DAM
A001 1 B001 1 15 15 ---B001单价是算出来的
A001 1 C001 2 5 10
A001 1 D001 1 5 5
http://topic.csdn.net/u/20090825/19/f1bddd50-ca59-431d-b4e3-745bba222798.html希望能有更优化的解决方案，谢谢！！
是呀，比较困难的，相信你们会帮我忙。
BOM最多是6阶的，一般都是控制在5阶之内，希望在查询时能在20秒内能查出某货品的理论成本价，
能帮我解决的再加100分，谢谢大家支持！
看在200分的份上， 决定献丑了
if object_id('[M_BOM]') is not null
begin
drop table [M_BOM] ;
end
GO
------------------------------------------
------------------------------------------
create table [M_BOM] (BILLID int,MGOODSID varchar(4),MQTY int, GOODSID varchar(4),ITEMNO int,QTY int,PRICE int);
go
------------------------------------------
------------------------------------------
insert into [M_BOM](BILLID ,MGOODSID ,MQTY , GOODSID ,ITEMNO ,QTY ,PRICE )
select 584 , 'A001' , 1 , 'B001' , 1 , 1 , NULL union all
select 584 , 'A001' , 1 , 'C001' , 2 , 2 , 5 union all
select 584 , 'A001' , 1 , 'D001' , 3 , 1 , 5 union all
select 588 , 'B001' , 1 , 'E001' , 1 , 1 , 3 union all
select 588 , 'B001' , 1 , 'F001' , 2 , 2 , NULL union all
select 560 , 'F001' , 1 , 'G001' , 1 , 1 , 2 union all
select 560 , 'F001' , 1 , 'J001' , 2 , 2 , 2
go
------------------------------------------
------------------------------------------
CREATE FUNCTION dbo.FUNC_AAAAA(@m_id varchar(4))
returns @rtb TABLE
(
MGOODSID varchar(4) COLLATE DATABASE_DEFAULT NULL,
MQTY int NULL,
DGOODSID varchar(4) COLLATE DATABASE_DEFAULT NULL,
DQTY int NULL,
DPRICE int NULL,
DAM int NULL
)
AS
BEGIN
declare @tmp_tb table(
MGOODSID varchar(4) COLLATE DATABASE_DEFAULT NULL,
MQTY int NULL,
DGOODSID varchar(4) COLLATE DATABASE_DEFAULT NULL,
DQTY int NULL,
DPRICE int NULL,
DAM int NULL,
LEV int NULL,
TPATH varchar(200) COLLATE DATABASE_DEFAULT NULL --BOM最多是6阶的，一般都是控制在5阶之内, 200足够了
);
declare @t_id varchar(4), @t_path varchar(200), @t_lev int; insert into @tmp_tb(MGOODSID, MQTY , DGOODSID , DQTY , DPRICE , DAM, TPATH, LEV)
select MGOODSID, MQTY , GOODSID , QTY , PRICE , -1, (MGOODSID + '-' +GOODSID),1 from [m_bom] where mgoodsid=@m_id; declare c1 CURSOR LOCAL FAST_FORWARD
FOR (
select DGOODSID,LEV,TPATH FROM @tmp_tb WHERE dam=(-1)
);
OPEN c1;
FETCH NEXT FROM c1 INTO @t_id,@t_lev,@t_path ;
WHILE @@FETCH_STATUS = 0
BEGIN
insert into @tmp_tb(MGOODSID, MQTY , DGOODSID , DQTY , DPRICE , DAM, TPATH,LEV)
select MGOODSID, MQTY , GOODSID , QTY , PRICE , -1,(@t_path + '-' + GOODSID),(@t_lev+1) from [m_bom] where mgoodsid=@t_id;
update @tmp_tb set DAM=0 where DGOODSID=@t_id and DAM=(-1);
FETCH NEXT FROM c1 INTO @t_id,@t_lev,@t_path ;
end
CLOSE c1;
DEALLOCATE c1; UPDATE @tmp_tb SET DAM = DQTY * DPRICE WHERE DPRICE IS NOT NULL; WHILE EXISTS(SELECT 1 FROM @tmp_tb where DPRICE is null)
begin
select top 1 @t_id=DGOODSID, @t_lev=LEV, @t_path=TPATH FROM @tmp_tb WHERE DPRICE is null order by LEV DESC; UPDATE @tmp_tb set DPRICE = (select sum(a.DAM) from @tmp_tb as a where charindex(@t_path,a.tpath)=1 and a.LEV=@t_lev+1)
where DGOODSID=@t_id and DPRICE is null ;
UPDATE @tmp_tb set DAM = DQTY * DPRICE where DGOODSID=@t_id;
end
insert into @rtb
select MGOODSID, MQTY , DGOODSID , DQTY , DPRICE , DAM from @tmp_tb ;
--insert into @rtb
-- select MGOODSID, MQTY , DGOODSID , DQTY , DPRICE , DAM from @tmp_tb order by TPATH ; return;
END
go
------------------------------------------
------------------------------------------
select * from dbo.FUNC_AAAAA('A001');
insert into [M_BOM](BILLID ,MGOODSID ,MQTY , GOODSID ,ITEMNO ,QTY ,PRICE )
declare @tmp_tb table(
MGOODSID varchar(4) COLLATE DATABASE_DEFAULT NULL,
MQTY int NULL,
DGOODSID varchar(4) COLLATE DATABASE_DEFAULT NULL,
DQTY int NULL,
DPRICE int NULL
怎么跟我原标题的不一样呢！运行脚本有如下提示:服务器: 消息 207，级别 16，状态 3，过程 FUNC_AAAAA，行 25
列名 'MGOODSID' 无效。
服务器: 消息 207，级别 16，状态 1，过程 FUNC_AAAAA，行 25
列名 'MQTY' 无效。
服务器: 消息 207，级别 16，状态 1，过程 FUNC_AAAAA，行 25
列名 'PRICE' 无效。
服务器: 消息 207，级别 16，状态 1，过程 FUNC_AAAAA，行 25
列名 'MGOODSID' 无效。
服务器: 消息 207，级别 16，状态 1，过程 FUNC_AAAAA，行 25
列名 'mgoodsid' 无效。
服务器: 消息 207，级别 16，状态 1，过程 FUNC_AAAAA，行 38
列名 'MGOODSID' 无效。
服务器: 消息 207，级别 16，状态 1，过程 FUNC_AAAAA，行 38
列名 'MQTY' 无效。
服务器: 消息 207，级别 16，状态 1，过程 FUNC_AAAAA，行 38
列名 'PRICE' 无效。
服务器: 消息 207，级别 16，状态 1，过程 FUNC_AAAAA，行 38
列名 'mgoodsid' 无效。
你给出这样的数据， 难道我下面这样建表有错？
if object_id('[M_BOM]') is not null
begin
drop table [M_BOM] ;
end
GO
------------------------------------------
------------------------------------------
create table [M_BOM] (BILLID int,MGOODSID varchar(4),MQTY int, GOODSID varchar(4),ITEMNO int,QTY int,PRICE int);
go
------------------------------------------
------------------------------------------
insert into [M_BOM](BILLID ,MGOODSID ,MQTY , GOODSID ,ITEMNO ,QTY ,PRICE )
select 584 , 'A001' , 1 , 'B001' , 1 , 1 , NULL union all
select 584 , 'A001' , 1 , 'C001' , 2 , 2 , 5 union all
select 584 , 'A001' , 1 , 'D001' , 3 , 1 , 5 union all
select 588 , 'B001' , 1 , 'E001' , 1 , 1 , 3 union all
select 588 , 'B001' , 1 , 'F001' , 2 , 2 , NULL union all
select 560 , 'F001' , 1 , 'G001' , 1 , 1 , 2 union all
select 560 , 'F001' , 1 , 'J001' , 2 , 2 , 2
go
但在我的数据库里面运算如下查询却没有出现任何数据
select * from dbo.FUNC_AAAAA('ZW2334');
但我在实际查询时却有数据的，不知道为什么?
SELECT * FROM BOM WHERE MGOODSID='ZW2334'
billid mgoodsid mqty dgoodsid itemno qty price
761 ZW2334 100.0 NC163 1 38.0 11.56
761 ZW2334 100.0 AV5 2 6.0 10.199999999999999
761 ZW2334 100.0 AR469 3 5.0 14.935
761 ZW2334 100.0 NC1070 4 11.300000000000001 15.25
761 ZW2334 100.0 NC1071 5 27.100000000000001 16.739999999999998
761 ZW2334 100.0 NC1080 6 4.5999999999999996 29.41
761 ZW2334 100.0 . 7 9.9999999999999995E-8 0.0
761 ZW2334 100.0 DR024 8 6.0 12.300000000000001
761 ZW2334 100.0 DR024 9 2.0 12.300000000000001
19 AV113 100.0 DR002 1 80.0 7.3500000000000005
19 AV113 100.0 DR113 2 20.0 31.93
20 AV114 100.0 DR002 1 79.5 7.3500000000000005
20 AV114 100.0 DR175 2 20.0 28.84
20 AV114 100.0 DR177 3 0.5 35.535000000000004
66 AV612 100.0 AV267 1 40.0 10.4
66 AV612 100.0 AR471 2 20.0 46.350000000000001
66 AV612 100.0 AV267 3 40.0 10.4
67 AV614 100.0 AV267 1 60.0 10.4
67 AV614 100.0 AR423 2 20.0 42.230000000000004
67 AV614 100.0 AV267 3 20.0 10.4
68 AV628 100.0 DR001 1 10.0 9.240000000000002
68 AV628 100.0 DR002 2 20.0 7.3500000000000005
68 AV628 100.0 AR426 3 20.0 54.590000000000003
68 AV628 100.0 AV168 4 50.0 15.68
69 AV804 100.0 DR001 1 13.0 9.240000000000002
69 AV804 100.0 DR002 2 30.5 7.3500000000000005
69 AV804 100.0 DR006 3 5.2000000000000002 9.5549999999999997
69 AV804 100.0 DR009 4 9.8000000000000007 6.1950000000000003
69 AV804 100.0 DR017 5 6.5 6.7200000000000006
69 AV804 100.0 DR108 6 35.0 27.810000000000002
70 G18X1 100.0 DR002 5 25.0 7.3500000000000005
70 G18X1 100.0 DR006 6 10.0 9.5549999999999997
70 G18X1 100.0 DR016 7 65.0 6.8300000000000001
71 G18X2 100.0 DR001 14 10.0 9.240000000000002
71 G18X2 100.0 DR002 15 25.0 7.3500000000000005
71 G18X2 100.0 DR009 16 12.0 6.1950000000000003
71 G18X2 100.0 DR016 17 53.0 6.8300000000000001
72 G18X3 100.0 DR001 1 28.0 9.240000000000002
72 G18X3 100.0 DR002 2 25.0 7.3500000000000005
72 G18X3 100.0 DR009 3 17.0 6.1950000000000003
72 G18X3 100.0 DR016 4 30.0 6.8300000000000001
73 G18X5 100.0 DR002 1 25.0 7.3500000000000005
73 G18X5 100.0 DR006 2 8.0 9.5549999999999997
73 G18X5 100.0 DR016 4 52.0 6.8300000000000001
73 G18X5 100.0 DR019 5 15.0 10.395000000000001
74 G18X6 100.0 DR001 1 10.0 9.240000000000002
74 G18X6 100.0 DR002 2 25.0 7.3500000000000005
74 G18X6 100.0 DR009 3 10.0 6.1950000000000003
74 G18X6 100.0 DR016 4 45.0 6.8300000000000001
74 G18X6 100.0 DR019 5 10.0 10.395000000000001
75 G18X7 100.0 DR001 1 38.0 9.240000000000002
75 G18X7 100.0 DR002 2 22.0 7.3500000000000005
75 G18X7 100.0 DR009 3 15.0 6.1950000000000003
75 G18X7 100.0 DR019 4 25.0 10.395000000000001
76 G18X10 100.0 DR002 1 22.0 7.3500000000000005
76 G18X10 100.0 DR006 2 10.0 9.5549999999999997
76 G18X10 100.0 DR019 3 20.0 10.395000000000001
76 G18X10 100.0 DR016 4 48.0 6.8300000000000001
77 G18X12 100.0 DR002 1 10.0 7.3500000000000005
77 G18X12 100.0 DR003 2 12.0 7.7700000000000005
77 G18X12 100.0 DR005 3 68.0 10.709999999999999
77 G18X12 100.0 DR006 4 10.0 9.5549999999999997
78 G18X18 100.0 DR001 1 19.0 9.240000000000002
78 G18X18 100.0 DR002 2 25.0 7.3500000000000005
78 G18X18 100.0 DR006 3 6.0 9.5549999999999997
78 G18X18 100.0 DR019 4 50.0 10.395000000000001
79 G18X19 100.0 DR001 1 19.0 9.240000000000002
79 G18X19 100.0 DR002 2 25.0 7.3500000000000005
79 G18X19 100.0 DR005 3 50.0 10.709999999999999
79 G18X19 100.0 DR009 4 6.0 6.1950000000000003
80 G18X20 100.0 DR002 1 25.0 7.3500000000000005
80 G18X20 100.0 DR006 2 3.0 9.5549999999999997
80 G18X20 100.0 DR009 3 7.0 6.1950000000000003
80 G18X20 100.0 DR019 4 65.0 10.395000000000001
81 G18X21 100.0 DR001 1 38.0 9.240000000000002
81 G18X21 100.0 DR002 2 22.0 7.3500000000000005
81 G18X21 100.0 DR005 3 25.0 10.709999999999999
81 G18X21 100.0 DR008 4 10.0 6.4050000000000002
81 G18X21 100.0 DR009 5 5.0 6.1950000000000003
82 NC143 100.0 DR008 1 42.0 6.4050000000000002
82 NC143 100.0 DR009 2 25.0 6.1950000000000003
82 NC143 100.0 NCH1/46 3 33.0 12.772
83 GFPX1 100.0 DR002 8 17.0 7.3500000000000005
83 GFPX1 100.0 DR016 9 63.0 6.8300000000000001
83 GFPX1 100.0 DR019 10 20.0 10.395000000000001
84 GFPX2 100.0 DR001 1 10.0 9.240000000000002
84 GFPX2 100.0 DR002 2 17.0 7.3500000000000005
84 GFPX2 100.0 DR019 3 20.0 10.395000000000001
84 GFPX2 100.0 DR016 4 53.0 6.8300000000000001
85 GFPX3 100.0 DR001 11 30.0 9.240000000000002
85 GFPX3 100.0 DR002 12 17.0 7.3500000000000005
85 GFPX3 100.0 DR016 13 53.0 6.8300000000000001
87 GPPX1 100.0 DR001 1 10.0 9.240000000000002
87 GPPX1 100.0 DR002 2 72.0 7.3500000000000005
87 GPPX1 100.0 DR006 3 6.0 9.5549999999999997
87 GPPX1 100.0 DR019 4 12.0 10.395000000000001
88 GPPX2 100.0 DR001 1 10.0 9.240000000000002
88 GPPX2 100.0 DR002 2 75.0 7.3500000000000005
88 GPPX2 100.0 DR009 3 10.0 6.1950000000000003
88 GPPX2 100.0 DR019 4 5.0 10.395000000000001
89 GPPX3 100.0 DR001 1 30.0 9.240000000000002
89 GPPX3 100.0 DR002 2 60.0 7.3500000000000005
89 GPPX3 100.0 DR009 3 10.0 6.1950000000000003
90 GPPX4 100.0 DR001 1 15.0 9.240000000000002
90 GPPX4 100.0 DR002 2 72.0 7.3500000000000005
90 GPPX4 100.0 DR006 3 3.0 9.5549999999999997
90 GPPX4 100.0 DR009 4 10.0 6.1950000000000003
91 RW101 100.0 DR008 1 40.0 6.4050000000000002
91 RW101 100.0 DR110 2 30.0 15.450000000000001
91 RW101 100.0 AR429 3 30.0 48.410000000000004
92 NC163 100.0 DR001 1 40.0 9.240000000000002
92 NC163 100.0 DR009 2 10.0 6.1950000000000003
92 NC163 100.0 DR024 3 12.0 12.300000000000001
92 NC163 100.0 NCH1/166 4 38.0 16.48
93 NC163-2 100.0 DR008 1 15.0 6.4050000000000002
93 NC163-2 100.0 DR009 2 19.5 6.1950000000000003
93 NC163-2 100.0 DR024 3 27.0 12.300000000000001
93 NC163-2 100.0 NCH1/166 4 38.5 16.48
92 NC163 100.0 DR001 1 40.0 9.240000000000002
92 NC163 100.0 DR009 2 10.0 6.1950000000000003
92 NC163 100.0 DR024 3 12.0 12.300000000000001
92 NC163 100.0 NCH1/166 4 38.0 16.48
137 NC163 100.0 DR001 1 40.0 9.240000000000002
137 NC163 100.0 DR009 2 10.0 6.1950000000000003
137 NC163 100.0 DR024 3 12.0 12.300000000000001
137 NC163 100.0 NCH1/166 4 38.0 16.48
736 ZW2343 100.0 NC163 1 13.0 11.56
736 ZW2343 100.0 AV5 2 6.0 10.199999999999999
736 ZW2343 100.0 AR469 3 5.0 14.935
736 ZW2343 100.0 DR023 4 5.0 12.300000000000001
736 ZW2343 100.0 NC1071 5 66.0 16.739999999999998
736 ZW2343 100.0 . 6 9.9999999999999995E-8 0.0
736 ZW2343 100.0 DR001 7 3.0 9.240000000000002
736 ZW2343 100.0 DR024 8 2.0 12.300000000000001
736 ZW2343 100.0 DR024 9 9.9999999999999995E-8 12.300000000000001
64 AV5 100.0 DR008 1 50.0 6.4050000000000002
64 AV5 100.0 DR110 2 50.0 15.450000000000001
65 AV5 100.0 DR008 1 50.0 6.4050000000000002
65 AV5 100.0 DR172 2 50.0 19.57
755 ZW2372 100.0 NC2048 4 1.8 21.059999999999999
755 ZW2372 100.0 NC1020 5 2.6000000000000001 15.300000000000001
755 ZW2372 100.0 . 6 9.9999999999999995E-8 0.0
755 ZW2372 100.0 DR023 7 5.0 12.300000000000001
755 ZW2372 100.0 DR024 8 7.0 12.300000000000001
755 ZW2372 100.0 DR024 9 1.6000000000000001 12.300000000000001
要不这样吧， 你把表结构给确定下来吧， 我们都不清楚你每个字段是什么类型啊，还有是一张表还是多张表。create table [M_BOM] -- 这里，你确认一下你的表名是不是M_BOM
(
BILLID int, -- 这个字段没有疑问， 应该是int的
MGOODSID varchar(4),
MQTY int, -- 这个字段的类型需要说清楚是什么类型， 从你的数据分析， 不是int的
GOODSID varchar(4),
ITEMNO int, -- 这个字段的类型需要说清楚是什么类型， 从你的数据分析， 不是int的
QTY int, -- 这个字段的类型需要说清楚是什么类型， 从你的数据分析， 不是int的
PRICE int -- 这个字段的类型需要说清楚是什么类型， 从你的数据分析， 不是int的);
花一些时间把它算下来，假如存到一个table里，再要求(2)不就直接select了吗？
如果你的目的只是为了求(2)，可以用展BOM的function，应该不会很慢吧？
BOM一共多少数据？
数据多了用展开bom的function是比较慢的， 还是得用空间换时间， 尽量把数据“挤”进局部内存临时表来计算。
给你参考一下,看你前面一个帖子,好像没有提到老大的这个函数,
好像老大的BLOG上面有新的算法,不过是基于2005的.--BOM算法
--产品配件清单查询示例(邹建)
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的方式，
如下是根据这个需求改过的，具体思路可参考，至少我用这种方式展50w资料的bom,针对约1w颗成品料一起展，约30秒不到
create table bom(billid int, p_id varchar(10), pqty int, c_id varchar(10),
itemno int, cqty int, dprice int)
insert into bom
select 584,'A001',1,'B001',1,1,null
union all select 584,'A001',1,'C001',2,2,5
union all select 584,'A001',1,'D001',3,1,5
union all select 588,'B001',1,'E001',1,1,3
union all select 588,'B001',1,'F001',2,2,null
union all select 560,'F001',1,'G001',1,1,2
union all select 560,'F001',1,'J001',2,2,null
union all select 560,'J001',1,'L001',1,3,4
union all select 560,'J001',1,'M001',2,1,5
union all select 560,'J001',1,'N001',2,1,3
GO
Create proc dbo.usp_bom
@p_id varchar(10)
AS
declare @loop int
set @loop=1
select p_id, pqty, c_id as tmp_id, c_id, cqty as tmp_qty,cqty,dprice, DAM=cqty*isnull(dprice,0)
into #bom
from bom
where p_id=@p_id --这边的where只是举例子，如抓的是多颗料，也是一样展开while (@loop>0)
BEGIN
select #bom.p_id,#bom.c_id as tmp_id, bom.c_id,bom.cqty,
DAM=sum(#bom.cqty*#bom.tmp_qty*(#bom.DAM+bom.cqty*isnull(bom.dprice,0)))
into #temp_bom
from #bom, bom
where #bom.c_id=bom.p_id
group by #bom.p_id,#bom.c_id , bom.c_id,bom.cqty
select #bom.p_id,#bom.pqty,#bom.tmp_id, #temp_bom.c_id,#bom.cqty as tmp_cqty,#temp_bom.cqty,#bom.dprice,#temp_bom.DAM
into #insert
from #bom,#temp_bom
where #bom.p_id=#temp_bom.p_id and #bom.c_id=#temp_bom.tmp_id delete #bom
from #bom, #temp_bom
where #bom.c_id=#temp_bom.tmp_id set @loop=@@rowcount insert into #bom select * from #insert drop table #temp_bom, #insert
END
--result
select #bom.p_id,#bom.pqty,#bom.tmp_id,
bom.cqty, sum(isnull(#bom.dprice,#bom.DAM)) as dprice,sum(#bom.DAM) as DAM
from #bom,bom
where #bom.p_id=bom.p_id and #bom.tmp_id=bom.c_id
group by #bom.p_id,#bom.pqty,#bom.tmp_id, bom.cqty
GOexec dbo.usp_bom 'A001'
/*
p_id pqty tmp_id cqty dprice DAM
---------- ----------- ---------- ----------- ----------- -----------
A001 1 B001 1 87 87
A001 1 C001 2 5 10
A001 1 D001 1 5 5
*/
GOdrop table bom
drop proc dbo.usp_bom
变成了一个视图 BOM
SQL codecreatetable[M_BOM]-- 这里，确认表名是BOM
BILLID int,--
MQTY FLOAT
GOODSID varchar(4),
ITEMNO int,--
QTY FLOAT,--
PRICE FLOAT-- 不好意思说得不清楚!!
把主表的结构也给出来吧， 你上面说的这个是分表的， 还有描述清楚主表跟分表是怎么关联的。
还有个问题， 见下面billid mgoodsid mqty dgoodsid itemno qty price
92 NC163 100.0 DR001 1 40.0 9.240000000000002 其中的price是什么值的时候表示该值是由它的下一级来计算的， 是null还是0？
--> 生成测试数据表:BOMIf not object_id('[BOM]') is null
Drop table [BOM]
Go
Create table [BOM]([BILLID] int,[MGOODSID] nvarchar(4),[MQTY] decimal(18,2),[DGOODSID] nvarchar(4),[ITEMNO] decimal(18,2),[DQTY] decimal(18,2),[DPRICE] decimal(18,2))
Insert BOM
Select 584,'A001',1.00,'B001',1.00,1.00,null union all
Select 584,'A001',1,'C001',2,2,5.00 union all
Select 584,'A001',1,'D001',3,1,5 union all
Select 588,'B001',1,'E001',1,1,3 union all
Select 588,'B001',1,'F001',2,2,null union all
Select 560,'F001',1,'G001',1,1,2 union all
Select 560,'F001',1,'J001',2,2,2
Go
--Select * from BOM-->SQL查询如下:If not object_id('[Fn_BOM]') is null
Drop function [Fn_BOM]
Go
Create Function Fn_BOM(@MGoodsID varchar(20))
returns @t table(MGOODSID varchar(20),MQTY dec(18,2),DGOODSID varchar(20),ITEMNO dec(18,2),DQTY dec(18,2),DPRICE dec(18,2),DAMT dec(18,2),lvl int)
as
begin
declare @lvl int
set @lvl=0
insert @t select MGOODSID,MQTY,DGOODSID,ITEMNO,DQTY,DPRICE,NULL,@lvl from BOM where MGoodsID=@MGoodsID
while @@rowcount>0
begin
set @lvl=@lvl+1
insert @t
select a.MGOODSID,a.MQTY,a.DGOODSID,a.ITEMNO,a.DQTY,a.DPRICE,a.DQTY*a.DPRICE,@lvl
from BOM a,@t b
where a.MGoodsID=b.DGoodsID
and b.lvl=@lvl-1
end
declare @i int
select @i=max(lvl) from @t where dprice is null
while @i>=0
begin
update a set
dprice=(select sum(damt) from @t where a.DGOODSID=MGOODSID),
damt=dqty*(select sum(damt) from @t where a.DGOODSID=MGOODSID)
from @t a
where lvl=@i
and dprice is null
set @i=@i-1
end
update @t set DAMT=DQTY*DPRICE where DAMT is null
return
end
go
select * from fn_bom('a001')
/*
MGOODSID MQTY DGOODSID ITEMNO DQTY DPRICE DAMT lvl
-------------------- --------------------------------------- -------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- -----------
A001 1.00 B001 1.00 1.00 15.00 15.00 0
A001 1.00 C001 2.00 2.00 5.00 10.00 0
A001 1.00 D001 3.00 1.00 5.00 5.00 0
B001 1.00 E001 1.00 1.00 3.00 3.00 1
B001 1.00 F001 2.00 2.00 6.00 12.00 1
F001 1.00 G001 1.00 1.00 2.00 2.00 2
F001 1.00 J001 2.00 2.00 2.00 4.00 2(7 行受影响)*/
select * from fn_bom('a001') where mgoodsid='a001'
/*
MGOODSID MQTY DGOODSID ITEMNO DQTY DPRICE DAMT lvl
-------------------- --------------------------------------- -------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- -----------
A001 1.00 B001 1.00 1.00 15.00 15.00 0
A001 1.00 C001 2.00 2.00 5.00 10.00 0
A001 1.00 D001 3.00 1.00 5.00 5.00 0(3 行受影响)*/
运行:
A001*1 ----B001*1 C001*1 D001*1
B001*1 ----E001*1 F001*1
F001*1 ----G001*1 J001*1
exec dbo.usp_bom 'A001' ---我这里计算是100个A001
(注意G001/J001/E001/C001/D001 测试时均把单价改为1 ，上下阶数量是1:1)
结果如下:
A001 100.0 B001 100.0 3.0 40004000000.0
A001 100.0 C001 100.0 1.0 100.0
A001 100.0 D001 100.0 1.0 100.040004000000.0这个不知道是怎么回事
100*3=300才对呀?
CREATE FUNCTION dbo.FUNC_AAAAA(@m_id varchar(4))
returns @rtb TABLE
(
MGOODSID varchar(4) COLLATE DATABASE_DEFAULT NULL,
MQTY float NULL,
DGOODSID varchar(4) COLLATE DATABASE_DEFAULT NULL,
DQTY float NULL,
DPRICE float NULL,
DAM float NULL
)
AS
BEGIN
declare @tmp_tb table(
MGOODSID varchar(4) COLLATE DATABASE_DEFAULT NULL,
MQTY float NULL,
DGOODSID varchar(4) COLLATE DATABASE_DEFAULT NULL,
DQTY float NULL,
DPRICE float NULL,
DAM float NULL,
LEV int NULL,
TPATH varchar(200) COLLATE DATABASE_DEFAULT NULL --BOM最多是6阶的，一般都是控制在5阶之内, 200足够了
);
declare @t_id varchar(4), @t_path varchar(200), @t_lev int; insert into @tmp_tb(MGOODSID, MQTY , DGOODSID , DQTY , DPRICE , DAM, TPATH, LEV)
select MGOODSID, MQTY , GOODSID , QTY , PRICE , -1, (MGOODSID + '-' +GOODSID),1 from [bom] where mgoodsid=@m_id; declare c1 CURSOR LOCAL FAST_FORWARD
FOR (
select DGOODSID,LEV,TPATH FROM @tmp_tb WHERE dam=(-1)
);
OPEN c1;
FETCH NEXT FROM c1 INTO @t_id,@t_lev,@t_path ;
WHILE @@FETCH_STATUS = 0
BEGIN
insert into @tmp_tb(MGOODSID, MQTY , DGOODSID , DQTY , DPRICE , DAM, TPATH,LEV)
select MGOODSID, MQTY , GOODSID , QTY , PRICE , -1,(@t_path + '-' + GOODSID),(@t_lev+1) from [bom] where mgoodsid=@t_id;
update @tmp_tb set DAM=0 where DGOODSID=@t_id and DAM=(-1);
FETCH NEXT FROM c1 INTO @t_id,@t_lev,@t_path ;
end
CLOSE c1;
DEALLOCATE c1; UPDATE @tmp_tb SET DAM = DQTY * DPRICE WHERE DPRICE IS NOT NULL; WHILE EXISTS(SELECT 1 FROM @tmp_tb where DPRICE is null)
begin
select top 1 @t_id=DGOODSID, @t_lev=LEV, @t_path=TPATH FROM @tmp_tb WHERE DPRICE is null order by LEV DESC; UPDATE @tmp_tb set DPRICE = (select sum(a.DAM) from @tmp_tb as a where charindex(@t_path,a.tpath)=1 and a.LEV=@t_lev+1)
where DGOODSID=@t_id and DPRICE is null ;
UPDATE @tmp_tb set DAM = DQTY * DPRICE where DGOODSID=@t_id;
end
insert into @rtb
select MGOODSID, MQTY , DGOODSID , DQTY , DPRICE , DAM from @tmp_tb ;
--insert into @rtb
-- select MGOODSID, MQTY , DGOODSID , DQTY , DPRICE , DAM from @tmp_tb order by TPATH ; return;
END
go
------------------------------------------
------------------------------------------
select * from dbo.FUNC_AAAAA('A001');
我想要的结果是计算成本单价 (B001=E001+G001+J001)今天星期天都在家测试，谢谢大家！
If not object_id('[Sp_BOM]') is null
Drop proc [Sp_BOM]
Go
Create proc Sp_BOM(@GOODSID varchar(20))
as
select MGOODSID,sum(MQTY) MQTY,sum(DAMT) DAMT
from fn_bom(@GOODSID)
where MGOODSID=@GOODSID
group by MGOODSID
GO
--调用过程
[Sp_BOM] 'B001'
/*
MGOODSID MQTY DAMT
-------------------- --------------------------------------- ---------------------------------------
B001 2.00 15.00(1 行受影响)
*/
[Sp_BOM] 'A001'
/*
MGOODSID MQTY DAMT
-------------------- --------------------------------------- ---------------------------------------
A001 3.00 30.00(1 行受影响)
*/
以下是在我实际数据库里面的测试，可以跟原题目有点出入.
select * from dbo.FUNC_AAAAA('A001');
A001 100.0 B001 100.0 1.0 100.0
A001 100.0 C001 100.0 1.0 100.0
A001 100.0 D001 100.0 1.0 100.0
B001 100.0 E001 100.0 1.0 100.0
B001 100.0 F001 100.0 1.0 100.0
F001 100.0 G001 100.0 1.0 100.0
F001 100.0 J001 100.0 1.0 100.0要求出的结果如下:
实际数据库中
A001*1 ----B001*1 C001*1 D001*1
B001*1 ----E001*1 F001*1
F001*1 ----G001*1 J001*1
---我这里计算是100个A001
(注意G001/J001/E001/C001/D001 测试时均把单价改为1 ，上下阶数量是1:1)
select * from dbo.FUNC_AAAAA('A001');
A001 100.0 B001 100.0 3 300
A001 100.0 C001 100.0 1.0 100.0
A001 100.0 D001 100.0 1.0 100.0
以下运行
[Sp_BOM] 'a001'A001 300.00 300.00[Sp_BOM] 'b001'
B001 200.00 200.00A001*1 ----B001*1 C001*1 D001*1
B001*1 ----E001*1 F001*1
F001*1 ----G001*1 J001*1
G001、J001、E001、C001、D001单价均为1时
---我这里计算是100个A001 ,100个B001
A001 应该是500才对
B001 应该是300才对
| | |
B001 100KG C001 100KG D001 100KG
| |
E001 100KG F001 100KG
| |
G001 100KG J001 100KG G001、J001、E001、C001、D001 单价都是1元/KG,作测试的。
谢谢,希望能继续改进一下，跟我想要的A001的成本单价有点出入.(B001=E001+G001+J001),
以下是运行你的脚本执行结果
select * from dbo.FUNC_AAAAA('A001');
A001 100.0 B001 100.0 1.0 100.0
A001 100.0 C001 100.0 1.0 100.0
A001 100.0 D001 100.0 1.0 100.0
B001 100.0 E001 100.0 1.0 100.0
B001 100.0 F001 100.0 1.0 100.0
F001 100.0 G001 100.0 1.0 100.0
F001 100.0 J001 100.0 1.0 100.0 要求出的正确结果如下:
select * from dbo.FUNC_AAAAA('A001');
A001 100.0 B001 100.0 3 300 ----------就是这里有点 B001=E001+G001+J001
A001 100.0 C001 100.0 1.0 100.0
A001 100.0 D001 100.0 1.0 100.0
B001成本价=GOO1成本价+J001成本价+E001成本价
=100*1 + 100 *1 + 100*1
=300元
--> 生成测试数据表:BOMIf not object_id('[BOM]') is null
Drop table [BOM]
Go
Create table [BOM]([BILLID] int,[MGOODSID] nvarchar(4),[MQTY] decimal(18,2),[DGOODSID] nvarchar(4),[ITEMNO] decimal(18,2),[DQTY] decimal(18,2),[DPRICE] decimal(18,2))
Insert BOM
Select 584,'A001',100.00,'B001',1.00,100.00,null union all
Select 584,'A001',100,'C001',2,100,1.00 union all
Select 584,'A001',100,'D001',3,100,1 union all
Select 588,'B001',100,'E001',1,100,1 union all
Select 588,'B001',100,'F001',2,100,null union all
Select 560,'F001',100,'G001',1,100,1 union all
Select 560,'F001',100,'J001',2,100,1
Go
--Select * from BOM-->SQL查询如下:If not object_id('[Fn_BOM]') is null
Drop function [Fn_BOM]
Go
Create Function Fn_BOM(@MGoodsID varchar(20))
returns @t table(MGOODSID varchar(20),MQTY dec(18,2),DGOODSID varchar(20),ITEMNO dec(18,2),DQTY dec(18,2),DPRICE dec(18,2),DAMT dec(18,2),lvl int)
as
begin
declare @lvl int
set @lvl=0
insert @t select MGOODSID,MQTY,DGOODSID,ITEMNO,DQTY,DPRICE,NULL,@lvl from BOM where MGoodsID=@MGoodsID
while @@rowcount>0
begin
set @lvl=@lvl+1
insert @t
select a.MGOODSID,a.MQTY,a.DGOODSID,a.ITEMNO,a.DQTY,a.DPRICE,a.DQTY*a.DPRICE,@lvl
from BOM a,@t b
where a.MGoodsID=b.DGoodsID
and b.lvl=@lvl-1
end
declare @i int
select @i=max(lvl) from @t where dprice is null
while @i>=0
begin
update a set
dprice=(select sum(dprice) from @t where a.DGOODSID=MGOODSID),
damt=dqty*(select sum(dprice) from @t where a.DGOODSID=MGOODSID)
from @t a
where lvl=@i
and dprice is null
set @i=@i-1
end
update @t set DAMT=DQTY*DPRICE where DAMT is null
return
end
go
select * from fn_bom('a001')
/*
MGOODSID MQTY DGOODSID ITEMNO DQTY DPRICE DAMT lvl
-------------------- --------------------------------------- -------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- -----------
A001 100.00 B001 1.00 100.00 3.00 300.00 0
A001 100.00 C001 2.00 100.00 1.00 100.00 0
A001 100.00 D001 3.00 100.00 1.00 100.00 0
B001 100.00 E001 1.00 100.00 1.00 100.00 1
B001 100.00 F001 2.00 100.00 2.00 200.00 1
F001 100.00 G001 1.00 100.00 1.00 100.00 2
F001 100.00 J001 2.00 100.00 1.00 100.00 2(7 行受影响)*/改下函数就行了。
--计算成本存储过程
If not object_id('[Sp_BOM]') is null
Drop proc [Sp_BOM]
Go
Create proc Sp_BOM(@GOODSID varchar(20))
as
select MGOODSID,sum(MQTY) MQTY,sum(DAMT) DAMT
from fn_bom(@GOODSID)
where MGOODSID=@GOODSID
group by MGOODSID
GO
--调用过程
[Sp_BOM] 'B001'
/*
MGOODSID MQTY DAMT
-------------------- --------------------------------------- ---------------------------------------
B001 200.00 300.00(1 行受影响)*/
[Sp_BOM] 'A001'
/*
MGOODSID MQTY DAMT
-------------------- --------------------------------------- ---------------------------------------
A001 300.00 500.00(1 行受影响)
*/
但是MQTY为什么会变了其它数量了，应该为原来的100数量，
怎么没有单价PRICE？ 数量*单价才=金额
--来个比较完整的。
--> 生成测试数据表:BOMIf not object_id('[BOM]') is null
Drop table [BOM]
Go
Create table [BOM]([BILLID] int,[MGOODSID] nvarchar(4),[MQTY] decimal(18,2),[DGOODSID] nvarchar(4),[ITEMNO] decimal(18,2),[DQTY] decimal(18,2),[DPRICE] decimal(18,2))
Insert BOM
Select 584,'A001',100.00,'B001',1.00,100.00,null union all
Select 584,'A001',100,'C001',2,100,1.00 union all
Select 584,'A001',100,'D001',3,100,1 union all
Select 588,'B001',100,'E001',1,100,1 union all
Select 588,'B001',100,'F001',2,100,null union all
Select 560,'F001',100,'G001',1,200,1 union all
Select 560,'F001',100,'J001',2,100,2
Go
--Select * from BOM-->SQL查询如下:If not object_id('[Fn_BOM]') is null
Drop function [Fn_BOM]
Go
Create Function Fn_BOM(@MGoodsID varchar(20))
returns @t table(MGOODSID varchar(20),MQTY dec(18,2),DGOODSID varchar(20),ITEMNO dec(18,2),DQTY dec(18,2),DPRICE dec(18,2),DAMT dec(18,2),lvl int)
as
begin
declare @lvl int
set @lvl=0
insert @t select MGOODSID,MQTY,DGOODSID,ITEMNO,DQTY,DPRICE,NULL,@lvl from BOM where MGoodsID=@MGoodsID
while @@rowcount>0
begin
set @lvl=@lvl+1
insert @t
select a.MGOODSID,a.MQTY,a.DGOODSID,a.ITEMNO,a.DQTY,a.DPRICE,a.DQTY*a.DPRICE,@lvl
from BOM a,@t b
where a.MGoodsID=b.DGoodsID
and b.lvl=@lvl-1
end
declare @i int
select @i=max(lvl) from @t where dprice is null
while @i>=0
begin
update a set
dprice=(select sum(damt) from @t where a.DGOODSID=MGOODSID)/dqty,
damt=(select sum(damt) from @t where a.DGOODSID=MGOODSID)
from @t a
where lvl=@i
and dprice is null
set @i=@i-1
end
update @t set DAMT=DQTY*DPRICE where DAMT is null
return
end
go
select * from fn_bom('a001')
/*
MGOODSID MQTY DGOODSID ITEMNO DQTY DPRICE DAMT lvl
-------------------- --------------------------------------- -------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- -----------
A001 100.00 B001 1.00 100.00 5.00 500.00 0
A001 100.00 C001 2.00 100.00 1.00 100.00 0
A001 100.00 D001 3.00 100.00 1.00 100.00 0
B001 100.00 E001 1.00 100.00 1.00 100.00 1
B001 100.00 F001 2.00 100.00 4.00 400.00 1
F001 100.00 G001 1.00 200.00 1.00 200.00 2
F001 100.00 J001 2.00 100.00 2.00 200.00 2(7 行受影响)*/--计算成本存储过程
If not object_id('[Sp_BOM]') is null
Drop proc [Sp_BOM]
Go
Create proc Sp_BOM(@GOODSID varchar(20))
as
select MGOODSID,sum(MQTY) MQTY,sum(DAMT) DAMT
from fn_bom(@GOODSID)
where MGOODSID=@GOODSID
group by MGOODSID
GO--调用过程
exec [Sp_BOM] 'B001'
/*
MGOODSID MQTY DAMT
-------------------- --------------------------------------- ---------------------------------------
B001 200.00 500.00(1 行受影响)
*/
exec [Sp_BOM] 'A001'
/*
MGOODSID MQTY DAMT
-------------------- --------------------------------------- ---------------------------------------
A001 300.00 700.00(1 行受影响)
*/
--计算成本存储过程
If not object_id('[Sp_BOM]') is null
Drop proc [Sp_BOM]
Go
Create proc Sp_BOM(@GOODSID varchar(20))
as
select MGOODSID,MQTY,sum(DAMT) DAMT
from fn_bom(@GOODSID)
where MGOODSID=@GOODSID
group by MGOODSID,MQTY
GO--调用过程
exec [Sp_BOM] 'B001'
/*
MGOODSID MQTY DAMT
-------------------- --------------------------------------- ---------------------------------------
B001 100.00 500.00(1 行受影响)
*/
exec [Sp_BOM] 'A001'
/*
MGOODSID MQTY DAMT
-------------------- --------------------------------------- ---------------------------------------
A001 100.00 700.00(1 行受影响)
*/
现帮我测试一下,谢谢!!
Create table [BOM]([BILLID] int,[MGOODSID] nvarchar(4),[MQTY] decimal(18,2),[DGOODSID] nvarchar(4),[ITEMNO] decimal(18,2),[DQTY] decimal(18,2),[DPRICE] decimal(18,2))
Insert BOM
Select 584,'A001',100.00,'B001',1.00,100.00,0.0 union all
Select 584,'A001',100.00,'C001',2,100,1.00 0.0 all
Select 584,'A001',100.00,'D001',3,100,1 union all
Select 588,'B001',100.00,'E001',1,100,1 union all
Select 588,'B001',100.00,'F001',2,100,0.0 union all
Select 560,'F001',100.00,'G001',1,100,1 union all
Select 560,'F001',100.00,'J001',2,100,1
Go录入相关数据后再执行,结果发现数量和金额有出入。
exec [Sp_BOM] 'B001'
B001 200.00 200.00exec [Sp_BOM] 'A001'
A001 300.00 300.00先休息一会吧，多谢你！
--> 生成测试数据表:BOMIf not object_id('[BOM]') is null
Drop table [BOM]
Go
Create table [BOM]([BILLID] int,[MGOODSID] nvarchar(4),[MQTY] decimal(18,2),[DGOODSID] nvarchar(4),[ITEMNO] decimal(18,2),[DQTY] decimal(18,2),[DPRICE] decimal(18,2))
Insert BOM
Select 584,'A001',100.00,'B001',1.00,100.00,0.0 union all
Select 584,'A001',100.00,'C001',2,100,1.00 union all
Select 584,'A001',100.00,'D001',3,100,1 union all
Select 588,'B001',100.00,'E001',1,100,1 union all
Select 588,'B001',100.00,'F001',2,100,0.0 union all
Select 560,'F001',100.00,'G001',1,100,1 union all
Select 560,'F001',100.00,'J001',2,100,1
Go
--Select * from BOM-->SQL查询如下:If not object_id('[Fn_BOM]') is null
Drop function [Fn_BOM]
Go
Create Function Fn_BOM(@MGoodsID varchar(20))
returns @t table(MGOODSID varchar(20),MQTY dec(18,2),DGOODSID varchar(20),ITEMNO dec(18,2),DQTY dec(18,2),DPRICE dec(18,2),DAMT dec(18,2),lvl int)
as
begin
declare @lvl int
set @lvl=0
insert @t select MGOODSID,MQTY,DGOODSID,ITEMNO,DQTY,DPRICE,NULL,@lvl from BOM where MGoodsID=@MGoodsID
while @@rowcount>0
begin
set @lvl=@lvl+1
insert @t
select a.MGOODSID,a.MQTY,a.DGOODSID,a.ITEMNO,a.DQTY,a.DPRICE,a.DQTY*a.DPRICE,@lvl
from BOM a,@t b
where a.MGoodsID=b.DGoodsID
and b.lvl=@lvl-1
end
declare @i int
select @i=max(lvl) from @t where dprice =0
while @i>=0
begin
update a set
dprice=(select sum(damt) from @t where a.DGOODSID=MGOODSID)/dqty,
damt=(select sum(damt) from @t where a.DGOODSID=MGOODSID)
from @t a
where lvl=@i
and dprice =0
set @i=@i-1
end
update @t set DAMT=DQTY*DPRICE where DAMT is null
return
end
go
select * from fn_bom('a001')
/*
MGOODSID MQTY DGOODSID ITEMNO DQTY DPRICE DAMT lvl
-------------------- --------------------------------------- -------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- -----------
A001 100.00 B001 1.00 100.00 3.00 300.00 0
A001 100.00 C001 2.00 100.00 1.00 100.00 0
A001 100.00 D001 3.00 100.00 1.00 100.00 0
B001 100.00 E001 1.00 100.00 1.00 100.00 1
B001 100.00 F001 2.00 100.00 2.00 200.00 1
F001 100.00 G001 1.00 100.00 1.00 100.00 2
F001 100.00 J001 2.00 100.00 1.00 100.00 2(7 行受影响)
*/--计算成本存储过程
If not object_id('[Sp_BOM]') is null
Drop proc [Sp_BOM]
Go
Create proc Sp_BOM(@GOODSID varchar(20))
as
select MGOODSID,MQTY,sum(DAMT) DAMT
from fn_bom(@GOODSID)
where MGOODSID=@GOODSID
group by MGOODSID,MQTY
GO--调用过程
exec [Sp_BOM] 'B001'
/*
MGOODSID MQTY DAMT
-------------------- --------------------------------------- ---------------------------------------
B001 100.00 300.00(1 行受影响)
*/
exec [Sp_BOM] 'A001'
/*
MGOODSID MQTY DAMT
-------------------- --------------------------------------- ---------------------------------------
A001 100.00 500.00(1 行受影响)
*/
MGOODIS MQTY DGOODSID DQTY DPRICE DAM
A001 100.0 B001 100.0 3 300
A001 100.0 C001 100.0 1.0 100.0
A001 100.0 D001 100.0 1.0 100.0
If not object_id('[Sp_BOM]') is null
Drop proc [Sp_BOM]
Go
Create proc Sp_BOM(@GOODSID varchar(20))
as
select MGOODSID,MQTY,DAMT
from fn_bom(@GOODSID)
where MGOODSID=@GOODSID
GO--调用过程
exec [Sp_BOM] 'B001'
/*
MGOODSID MQTY DAMT
-------------------- --------------------------------------- ---------------------------------------
B001 100.00 100.00
B001 100.00 200.00(2 行受影响)
*/
exec [Sp_BOM] 'A001'
/*
MGOODSID MQTY DAMT
-------------------- --------------------------------------- ---------------------------------------
A001 100.00 300.00
A001 100.00 100.00
A001 100.00 100.00(3 行受影响)
*/
--计算成本存储过程
If not object_id('[Sp_BOM]') is null
Drop proc [Sp_BOM]
Go
Create proc Sp_BOM(@GOODSID varchar(20))
as
select MGOODSID,MQTY,DGOODSID ,DQTY,DPRICE,DAMT
from fn_bom(@GOODSID)
where MGOODSID=@GOODSID
GO--调用过程
exec [Sp_BOM] 'B001'
/*
MGOODSID MQTY DGOODSID DQTY DPRICE DAMT
-------------------- --------------------------------------- -------------------- --------------------------------------- --------------------------------------- ---------------------------------------
B001 100.00 E001 100.00 1.00 100.00
B001 100.00 F001 100.00 2.00 200.00(2 行受影响)
*/
exec [Sp_BOM] 'A001'
/*
MGOODSID MQTY DGOODSID DQTY DPRICE DAMT
-------------------- --------------------------------------- -------------------- --------------------------------------- --------------------------------------- ---------------------------------------
A001 100.00 B001 100.00 3.00 300.00
A001 100.00 C001 100.00 1.00 100.00
A001 100.00 D001 100.00 1.00 100.00(3 行受影响)
*/
CREATE FUNCTION dbo.FUNC_AAAAA(@m_id varchar(4))
returns @rtb TABLE
(
MGOODSID varchar(4) COLLATE DATABASE_DEFAULT NULL,
MQTY decimal(18,2) NULL,
DGOODSID varchar(4) COLLATE DATABASE_DEFAULT NULL,
DQTY decimal(18,2) NULL,
DPRICE decimal(18,2) NULL,
DAM decimal(18,2) NULL
)
AS
BEGIN
declare @tmp_tb table(
MGOODSID varchar(4) COLLATE DATABASE_DEFAULT NULL,
MQTY decimal(18,2) NULL,
DGOODSID varchar(4) COLLATE DATABASE_DEFAULT NULL,
DQTY decimal(18,2) NULL,
DPRICE decimal(18,2) NULL,
DAM decimal(18,2) NULL,
LEV int NULL,
TPATH varchar(200) COLLATE DATABASE_DEFAULT NULL --BOM最多是6阶的，一般都是控制在5阶之内, 200足够了
);
declare @t_id varchar(4), @t_path varchar(200), @t_lev int; insert into @tmp_tb(MGOODSID, MQTY , DGOODSID , DQTY , DPRICE , DAM, TPATH, LEV)
select MGOODSID, MQTY , DGOODSID , DQTY , DPRICE , -1, (MGOODSID + '-' +DGOODSID),1 from [bom] where mgoodsid=@m_id; declare c1 CURSOR LOCAL FAST_FORWARD
FOR (
select DGOODSID,LEV,TPATH FROM @tmp_tb WHERE dam=(-1)
);
OPEN c1;
FETCH NEXT FROM c1 INTO @t_id,@t_lev,@t_path ;
WHILE @@FETCH_STATUS = 0
BEGIN
insert into @tmp_tb(MGOODSID, MQTY , DGOODSID , DQTY , DPRICE , DAM, TPATH,LEV)
select MGOODSID, MQTY , DGOODSID , DQTY , DPRICE , -1,(@t_path + '-' + DGOODSID),(@t_lev+1) from [bom] where mgoodsid=@t_id;
update @tmp_tb set DAM=0 where DGOODSID=@t_id and DAM=(-1);
FETCH NEXT FROM c1 INTO @t_id,@t_lev,@t_path ;
end
CLOSE c1;
DEALLOCATE c1; UPDATE @tmp_tb SET DAM = DQTY * DPRICE WHERE DPRICE<>0; WHILE EXISTS(SELECT 1 FROM @tmp_tb where DPRICE =0)
begin
select top 1 @t_id=DGOODSID, @t_lev=LEV, @t_path=TPATH FROM @tmp_tb WHERE DPRICE =0 order by LEV DESC; UPDATE @tmp_tb set DPRICE = (select sum(a.DAM) from @tmp_tb as a where charindex(@t_path,a.tpath)=1 and a.LEV=@t_lev+1)
where DGOODSID=@t_id and DPRICE = 0 ;
UPDATE @tmp_tb set DAM = DQTY * DPRICE where DGOODSID=@t_id;
end
insert into @rtb
select MGOODSID, MQTY , DGOODSID , DQTY , DPRICE , DAM from @tmp_tb ;
--insert into @rtb
-- select MGOODSID, MQTY , DGOODSID , DQTY , DPRICE , DAM from @tmp_tb order by TPATH ; return;
END
go
------------------------------------------
------------------------------------------
select * from dbo.FUNC_AAAAA('A001'); A001 100.00 B001 100.00 20100.00 2010000.00
A001 100.00 C001 100.00 1.00 100.00
A001 100.00 D001 100.00 1.00 100.00
B001 100.00 E001 100.00 1.00 100.00
B001 100.00 F001 100.00 200.00 20000.00
F001 100.00 G001 100.00 1.00 100.00
F001 100.00 J001 100.00 1.00 100.00
/*
以你提供的数据为例子：
BOM表
产品 产品数量 原料 原料数 单价
BILLID MGOODSID MQTY DGOODSID ITEMNO DQTY DPRICE
584 A001 1 B001 1 1 ?? (半成品)
584 A001 1 C001 2 2 5 (原料) 2个C001组成
584 A001 1 D001 3 1 5 (原料)
588 B001 1 E001 1 1 3 (原料)
588 B001 1 F001 2 2 ?? (半成品) 2个F001组成
560 F001 1 G001 1 1 2 (原料)
560 F001 1 J001 2 2 2 (原料) 2个J001组成 */
declare @m_id varchar(4);
set @m_id = 'A001'; declare @tmp_tb table(
MGOODSID varchar(4) COLLATE DATABASE_DEFAULT NULL,
MQTY float NULL,
DGOODSID varchar(4) COLLATE DATABASE_DEFAULT NULL,
DQTY float NULL,
DPRICE float NULL,
DAM float NULL,
LEV int NULL,
TPATH varchar(200) COLLATE DATABASE_DEFAULT NULL --BOM最多是6阶的，一般都是控制在5阶之内, 200足够了
);
declare @t_id varchar(4), @t_path varchar(200), @t_lev int; insert into @tmp_tb(MGOODSID, MQTY , DGOODSID , DQTY , DPRICE , DAM, TPATH, LEV)
select MGOODSID, MQTY , GOODSID , QTY , PRICE , -1, (MGOODSID + '-' +GOODSID),1 from [m_bom] where mgoodsid=@m_id;
/*
这一步，执行select * from @tmp_tb; 把数据打出来，此时数据应该是
A001 1 B001 1 NULL -1 1 A001-B001
A001 1 C001 2 5 -1 1 A001-C001
A001 1 D001 1 5 -1 1 A001-D001
DAM=(-1)的记录是需要向下检索子结点的
*/
declare c1 CURSOR LOCAL FAST_FORWARD
FOR (
select DGOODSID,LEV,TPATH FROM @tmp_tb WHERE dam=(-1)
);
OPEN c1;
FETCH NEXT FROM c1 INTO @t_id,@t_lev,@t_path ;
WHILE @@FETCH_STATUS = 0
BEGIN
insert into @tmp_tb(MGOODSID, MQTY , DGOODSID , DQTY , DPRICE , DAM, TPATH,LEV)
select MGOODSID, MQTY , GOODSID , QTY , PRICE , -1,(@t_path + '-' + GOODSID),(@t_lev+1) from [m_bom] where mgoodsid=@t_id;
/*
这一步，以第一次循环@t_id='B001'为例子， 是把它的子结点检索出来,添加进入@tmp_tb， 这里进行了2个出来，路径和所在树的层的确定。
数据应该是下面的
A001 1 B001 1 NULL -1 1 A001-B001
A001 1 C001 2 5 -1 1 A001-C001
A001 1 D001 1 5 -1 1 A001-D001
B001 1 E001 1 3 -1 2 A001-B001-E001
B001 1 F001 2 NULL -1 2 A001-B001-F001
这样B001的子结点就被加载到内存表了， 它们是否有字节点，这一步不用关， 等游标检索到它们的时候再处理
*/
update @tmp_tb set DAM=0 where DGOODSID=@t_id and DAM=(-1);
/*
这一步，@t_id='B001'已经进行过检索下一级的结点操作了， 下次不需要出现在游标中了， 把它的状态置为0
此时的数据为
A001 1 B001 1 NULL 0 1 A001-B001
A001 1 C001 2 5 -1 1 A001-C001
A001 1 D001 1 5 -1 1 A001-D001
B001 1 E001 1 3 -1 2 A001-B001-E001
B001 1 F001 2 NULL -1 2 A001-B001-F001
这样B001的子结点就被加载到内存表了， 它们是否有字节点，这一步不用关， 等游标检索到它们的时候再处理。
*/
FETCH NEXT FROM c1 INTO @t_id,@t_lev,@t_path ;
end
CLOSE c1;
DEALLOCATE c1;
/*
执行到这一步，@t_id='A001'下面所有的子结点， 包括字节点的子结点， 统统应该都被加载到@tmp_tb里面
此时的数据为
A001 1 B001 1 NULL 0 1 A001-B001
A001 1 C001 2 5 0 1 A001-C001
A001 1 D001 1 5 0 1 A001-D001
B001 1 E001 1 3 0 2 A001-B001-E001
B001 1 F001 2 NULL 0 2 A001-B001-F001
F001 1 G001 1 2 0 3 A001-B001-F001-G001
F001 1 J001 2 2 0 3 A001-B001-F001-J001
*/ UPDATE @tmp_tb SET DAM = DQTY * DPRICE WHERE DPRICE IS NOT NULL;
/*
这一步，是将所以price不是null的视为最基础的结点（注意是dprice=null, 如果不是null， 就要改成其他的条件) 计算它们。
从你提供的数据， 我觉得我分析的没错， 如计算方法有误， 请说明白，改改
此时的数据为
A001 1 B001 1 NULL 0 1 A001-B001
A001 1 C001 2 5 10 1 A001-C001
A001 1 D001 1 5 5 1 A001-D001
B001 1 E001 1 3 3 2 A001-B001-E001
B001 1 F001 2 NULL 0 2 A001-B001-F001
F001 1 G001 1 2 2 3 A001-B001-F001-G001
F001 1 J001 2 2 4 3 A001-B001-F001-J001
这样， 我们就可以从最底层往上面计算了
*/ WHILE EXISTS(SELECT 1 FROM @tmp_tb where DPRICE is null)
begin
select top 1 @t_id=DGOODSID, @t_lev=LEV, @t_path=TPATH FROM @tmp_tb WHERE DPRICE is null order by LEV DESC;
/*
这一步，是确定最底层的需要计算的结点， 也就是price是NULL的， 样本数据中， 第一次选取的应该是@t_id='F001'。
*/ UPDATE @tmp_tb set DPRICE = (select sum(a.DAM) from @tmp_tb as a where charindex(@t_path,a.tpath)=1 and a.LEV=@t_lev+1)
where DGOODSID=@t_id and DPRICE is null ;
/*
根据上一步确定的@t_id, 计算单价， 从提供的样本数据分析， 计算公式是（price=下一级子结点DAM的和）
不知道理解是不是对了， 如果不对， 改改
以@t_id='F001', 为例子， 它的单价应该是 G001的dam ＋ J001的dam
*/
UPDATE @tmp_tb set DAM = DQTY * DPRICE where DGOODSID=@t_id;
/*
上一步计算好单价， 这一步乘以数量计算总价
不知道理解是不是对了， 如果不对， 改改
*/
end
/*
这一步，处理完成了
此时的数据为
A001 1 B001 1 15 15 1 A001-B001
A001 1 C001 2 5 10 1 A001-C001
A001 1 D001 1 5 5 1 A001-D001
B001 1 E001 1 3 3 2 A001-B001-E001
B001 1 F001 2 6 12 2 A001-B001-F001
F001 1 G001 1 2 2 3 A001-B001-F001-G001
F001 1 J001 2 2 4 3 A001-B001-F001-J001
*/
--insert into @rtb
-- select MGOODSID, MQTY , DGOODSID , DQTY , DPRICE , DAM from @tmp_tb ;
--insert into @rtb
-- select MGOODSID, MQTY , DGOODSID , DQTY , DPRICE , DAM from @tmp_tb order by TPATH ;
select MGOODSID, MQTY , DGOODSID , DQTY , DPRICE , DAM from @tmp_tb ;
/*
这一步，提取需要返回的数据
此时的数据为
A001 1 B001 1 15 15
A001 1 C001 2 5 10
A001 1 D001 1 5 5
B001 1 E001 1 3 3
B001 1 F001 2 6 12
F001 1 G001 1 2 2
F001 1 J001 2 2 4
*/
go
------------------------------------------
------------------------------------------
playwarcraft兄在吗？这里能不能再帮忙处理一下，这个方法也不错呀！
p_id pqty tmp_id cqty dprice DAM
---------- ----------- ---------- ----------- ----------- -----------
A001 1 B001 1 87 87 -----这里不正确呀!
A001 1 C001 2 5 10
A001 1 D001 1 5 5*/GOdroptable bomdropproc
白天使用这个bom树页面，是非常快，只是信息相当于最多滞后一天
但还有个事情希望能防止漏洞出现。
如果货品B001在DPRICE字段里不是0的，或修改过该货品时，
计算时就会有问题。
同理F001也是如此。不信的话可以在F001或B001原始资料资料中的DPRICE字段填入某个数字，就会导致计算不正确。
SQL code
--> 生成测试数据表:BOMIf not object_id('[BOM]') is null
Drop table [BOM]
Go
Create table [BOM]([BILLID] int,[MGOODSID] nvarchar(4),[MQTY] decimal(18,2),[DGOODSID] nvarchar(4),[ITEMNO] decimal(18,2),[DQTY] decimal(18,2),[DPRICE] decimal(18,2))
Insert BOM
Select 584,'A001',100.00,'B001',1.00,100.00,0.0 union all
Select 584,'A001',100.00,'C001',2,100,1.00 union all
Select 584,'A001',100.00,'D001',3,100,1 union all
Select 588,'B001',100.00,'E001',1,100,1 union all
Select 588,'B001',100.00,'F001',2,100,0.0 union all
Select 560,'F001',100.00,'G001',1,100,1 union all
Select 560,'F001',100.00,'J001',2,100,1
Go
--Select * from BOM-->SQL查询如下:If not object_id('[Fn_BOM]') is null
Drop function [Fn_BOM]
Go
Create Function Fn_BOM(@MGoodsID varchar(20))
returns @t table(MGOODSID varchar(20),MQTY dec(18,2),DGOODSID varchar(20),ITEMNO dec(18,2),DQTY dec(18,2),DPRICE dec(18,2),DAMT dec(18,2),lvl int)
as
begin
declare @lvl int
set @lvl=0
insert @t select MGOODSID,MQTY,DGOODSID,ITEMNO,DQTY,DPRICE,NULL,@lvl from BOM where MGoodsID=@MGoodsID
while @@rowcount>0
begin
set @lvl=@lvl+1
insert @t
select a.MGOODSID,a.MQTY,a.DGOODSID,a.ITEMNO,a.DQTY,a.DPRICE,a.DQTY*a.DPRICE,@lvl
from BOM a,@t b
where a.MGoodsID=b.DGoodsID
and b.lvl=@lvl-1
end
declare @i int
select @i=max(lvl) from @t where dprice =0
while @i>=0
begin
update a set
dprice=(select sum(damt) from @t where a.DGOODSID=MGOODSID)/dqty,
damt=(select sum(damt) from @t where a.DGOODSID=MGOODSID)
from @t a
where lvl=@i
and dprice =0
set @i=@i-1
end
update @t set DAMT=DQTY*DPRICE where DAMT is null
return
end
go
select * from fn_bom('a001')
/*
MGOODSID MQTY DGOODSID ITEMNO DQTY DPRICE DAMT lvl
-------------------- --------------------------------------- -------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- -----------
A001 100.00 B001 1.00 100.00 3.00 300.00 0
A001 100.00 C001 2.00 100.00 1.00 100.00 0
A001 100.00 D001 3.00 100.00 1.00 100.00 0
B001 100.00 E001 1.00 100.00 1.00 100.00 1
B001 100.00 F001 2.00 100.00 2.00 200.00 1
F001 100.00 G001 1.00 100.00 1.00 100.00 2
F001 100.00 J001 2.00 100.00 1.00 100.00 2(7 行受影响)
*/
SQL code--计算成本存储过程
If not object_id('[Sp_BOM]') is null
Drop proc [Sp_BOM]
Go
Create proc Sp_BOM(@GOODSID varchar(20))
as
select MGOODSID,MQTY,DGOODSID ,DQTY,DPRICE,DAMT
from fn_bom(@GOODSID)
where MGOODSID=@GOODSID
GO--调用过程
exec [Sp_BOM] 'B001'
/*
MGOODSID MQTY DGOODSID DQTY DPRICE DAMT
-------------------- --------------------------------------- -------------------- --------------------------------------- --------------------------------------- ---------------------------------------
B001 100.00 E001 100.00 1.00 100.00
B001 100.00 F001 100.00 2.00 200.00(2 行受影响)
*/
exec [Sp_BOM] 'A001'
/*
MGOODSID MQTY DGOODSID DQTY DPRICE DAMT
-------------------- --------------------------------------- -------------------- --------------------------------------- --------------------------------------- ---------------------------------------
A001 100.00 B001 100.00 3.00 300.00
A001 100.00 C001 100.00 1.00 100.00
A001 100.00 D001 100.00 1.00 100.00(3 行受影响)
*/请测试：
Insert BOM
Select 584,'A001',100.00,'B001',1.00,100.00,1 union all
Select 584,'A001',100.00,'C001',2,100,1.00 union all
Select 584,'A001',100.00,'D001',3,100,1 union all
Select 588,'B001',100.00,'E001',1,100,1 union all
Select 588,'B001',100.00,'F001',2,100,1 union all
Select 560,'F001',100.00,'G001',1,100,1 union all
Select 560,'F001',100.00,'J001',2,100,1
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-08-29 16:57:24
---------------------------------
--> 生成测试数据表:BOMIf not object_id('[BOM]') is null
Drop table [BOM]
Go
Create table [BOM]([BILLID] int,[MGOODSID] nvarchar(4),[MQTY] decimal(18,2),[DGOODSID] nvarchar(4),[ITEMNO] decimal(18,2),[DQTY] decimal(18,2),[DPRICE] decimal(18,2))
Insert BOM
Select 584,'A001',100.00,'B001',1.00,100.00,1 union all
Select 584,'A001',100.00,'C001',2,100,1.00 union all
Select 584,'A001',100.00,'D001',3,100,1 union all
Select 588,'B001',100.00,'E001',1,100,1 union all
Select 588,'B001',100.00,'F001',2,100,1 union all
Select 560,'F001',100.00,'G001',1,100,1 union all
Select 560,'F001',100.00,'J001',2,100,1 Go
--Select * from BOM-->SQL查询如下:If not object_id('[Fn_BOM]') is null
Drop function [Fn_BOM]
Go
Create Function Fn_BOM(@MGoodsID varchar(20))
returns @t table(MGOODSID varchar(20),MQTY dec(18,2),DGOODSID varchar(20),ITEMNO dec(18,2),DQTY dec(18,2),DPRICE dec(18,2),DAMT dec(18,2),lvl int)
as
begin
declare @lvl int
set @lvl=0
insert @t select MGOODSID,MQTY,DGOODSID,ITEMNO,DQTY,DPRICE,NULL,@lvl from BOM where MGoodsID=@MGoodsID
while @@rowcount>0
begin
set @lvl=@lvl+1
insert @t
select a.MGOODSID,a.MQTY,a.DGOODSID,a.ITEMNO,a.DQTY,a.DPRICE,a.DQTY*a.DPRICE,@lvl
from BOM a,@t b
where a.MGoodsID=b.DGoodsID
and b.lvl=@lvl-1
end
declare @i int
select @i=max(lvl) from @t t where exists(select 1 from @t where t.dgoodsid=mgoodsid)
while @i>=0
begin
update a set
dprice=(select sum(damt) from @t where a.DGOODSID=MGOODSID)/dqty,
damt=(select sum(damt) from @t where a.DGOODSID=MGOODSID)
from @t a
where lvl=@i
and exists(select 1 from @t where a.dgoodsid=mgoodsid)
set @i=@i-1
end
update @t set DAMT=DQTY*DPRICE where DAMT is null
return
end
go
select * from fn_bom('a001')
/*
MGOODSID MQTY DGOODSID ITEMNO DQTY DPRICE DAMT lvl
-------------------- --------------------------------------- -------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- -----------
A001 100.00 B001 1.00 100.00 3.00 300.00 0
A001 100.00 C001 2.00 100.00 1.00 100.00 0
A001 100.00 D001 3.00 100.00 1.00 100.00 0
B001 100.00 E001 1.00 100.00 1.00 100.00 1
B001 100.00 F001 2.00 100.00 2.00 200.00 1
F001 100.00 G001 1.00 100.00 1.00 100.00 2
F001 100.00 J001 2.00 100.00 1.00 100.00 2(7 行受影响)
*/--计算成本存储过程
If not object_id('[Sp_BOM]') is null
Drop proc [Sp_BOM]
Go
Create proc Sp_BOM(@GOODSID varchar(20))
as
select MGOODSID,MQTY,DGOODSID ,DQTY,DPRICE,DAMT
from fn_bom(@GOODSID)
where MGOODSID=@GOODSID
GO--调用过程
exec [Sp_BOM] 'B001'
/*
MGOODSID MQTY DGOODSID DQTY DPRICE DAMT
-------------------- --------------------------------------- -------------------- --------------------------------------- --------------------------------------- ---------------------------------------
B001 100.00 E001 100.00 1.00 100.00
B001 100.00 F001 100.00 2.00 200.00(2 行受影响)
*/
exec [Sp_BOM] 'A001'
/*
MGOODSID MQTY DGOODSID DQTY DPRICE DAMT
-------------------- --------------------------------------- -------------------- --------------------------------------- --------------------------------------- ---------------------------------------
A001 100.00 B001 100.00 3.00 300.00
A001 100.00 C001 100.00 1.00 100.00
A001 100.00 D001 100.00 1.00 100.00(3 行受影响)
*/
update @t set DAMT=DQTY*DPRICE where DAMT is null
exec [Sp_BOM] 'NC163'
MGOODSID MQTY DGOODSID DQTY DPRICE DAMT
NC163 100.00 DR001 40.00 9.24 369.60
NC163 100.00 DR009 10.00 6.20 62.00
NC163 100.00 DR024 12.00 12.30 147.60
NC163 100.00 NCH1/166 38.00 16.48 626.24-------以上DAMT合计=1205.44 应该单价是 1205.44/100=12.05exec [Sp_BOM] 'NC451'
MGOODSID MQTY DGOODSID DQTY DPRICE DAMT
NC451 100.00 NC163 15.00 80.36 1205.39 ----这个80.36有点不对吧！
NC451 100.00 DR009 20.00 6.20 124.00
NC451 100.00 DR024 30.00 12.30 369.00
NC451 100.00 DR023 5.00 12.30 61.50
NC451 100.00 AR451 30.00 28.84 865.20
select 1205.39/15.00
/*---------------------------------------
80.3593333(1 行受影响)
*/
----B001100.00 F001100.00200.0020000.00 单价没有哪么高，
下阶的成本单价=DAMT/MQTY ,传到上阶的应该是单位成本单价才行。
谢谢!
另外在我的数据库里面运行:是没有任务数据出现的
select * from dbo.FUNC_AAAAA('NC163'); select * from dbo.FUNC_AAAAA('NC451');
但运行以下
select * from dbo.FUNC_AAAAA('A001');
select * from dbo.FUNC_AAAAA('F001');
select * from dbo.FUNC_AAAAA('B001');
都有数据出来的。
谢谢关注：
下阶清单: 这里做100KG NC163 才耗用1205.44元，所以单位成本价/KG应该是 1205.44/100KG=12.05元
MGOODSID MQTY DGOODSID DQTY DPRICE DAMT
NC163 100.00 DR001 40.00 9.24 369.60
NC163 100.00 DR009 10.00 6.20 62.00
NC163 100.00 DR024 12.00 12.30 147.60
NC163 100.00 NCH1/166 38.00 16.48 626.24
exec [Sp_BOM] 'NC163'
MGOODSID MQTY DGOODSID DQTY DPRICE DAMT
NC163 100.00 DR001 40.00 9.24 369.60
NC163 100.00 DR009 10.00 6.20 62.00
NC163 100.00 DR024 12.00 12.30 147.60
NC163 100.00 NCH1/166 38.00 16.48 626.24 -------以上DAMT合计=1205.44 应该单价是 1205.44/100=12.05 算出来的单位成本价exec [Sp_BOM] 'NC451' -----NC451引用NC163 ，哪么引用NC163的单位成本单价到NC451 DPRICE
MGOODSID MQTY DGOODSID DQTY DPRICE DAMT
NC451 100.00 NC163 15.00 80.36 1205.39 ----这个80.36有点不对吧！ 这里DPRICE=12.05
NC451 100.00 DR009 20.00 6.20 124.00
NC451 100.00 DR024 30.00 12.30 369.00
NC451 100.00 DR023 5.00 12.30 61.50
NC451 100.00 AR451 30.00 28.84 865.20 看看NC163是作为半成品，NC451作为成品，所以NC451取NC163的单位成本价
--> 生成测试数据表:BOMIf not object_id('[BOM]') is null
Drop table [BOM]
Go
Create table [BOM]([BILLID] int,[MGOODSID] nvarchar(4),[MQTY] decimal(18,2),[DGOODSID] nvarchar(4),[ITEMNO] decimal(18,2),[DQTY] decimal(18,2),[DPRICE] decimal(18,2))
Insert BOM
Select 584,'A001',100.00,'B001',1.00,100.00,1 union all
Select 584,'A001',100.00,'C001',2,100,1.00 union all
Select 584,'A001',100.00,'D001',3,100,1 union all
Select 588,'B001',100.00,'E001',1,100,1 union all
Select 588,'B001',100.00,'F001',2,100,1 union all
Select 560,'F001',100.00,'G001',1,100,1 union all
Select 560,'F001',100.00,'J001',2,100,1 Go
--Select * from BOM-->SQL查询如下:If not object_id('[Fn_BOM]') is null
Drop function [Fn_BOM]
Go
Create Function Fn_BOM(@MGoodsID varchar(20))
returns @t table(MGOODSID varchar(20),MQTY dec(18,2),DGOODSID varchar(20),ITEMNO dec(18,2),DQTY dec(18,2),DPRICE dec(18,2),DAMT dec(18,2),lvl int)
as
begin
declare @lvl int
set @lvl=0
insert @t select MGOODSID,MQTY,DGOODSID,ITEMNO,DQTY,DPRICE,NULL,@lvl from BOM where MGoodsID=@MGoodsID
while @@rowcount>0
begin
set @lvl=@lvl+1
insert @t
select a.MGOODSID,a.MQTY,a.DGOODSID,a.ITEMNO,a.DQTY,a.DPRICE,a.DQTY*a.DPRICE,@lvl
from BOM a,@t b
where a.MGoodsID=b.DGoodsID
and b.lvl=@lvl-1
end
declare @i int
select @i=max(lvl) from @t t where exists(select 1 from @t where t.dgoodsid=mgoodsid)
while @i>=0
begin
update a set
dprice=(select sum(damt) from @t where a.DGOODSID=MGOODSID)/mqty, --这里改下测试
damt=(select sum(damt) from @t where a.DGOODSID=MGOODSID)
from @t a
where lvl=@i
and exists(select 1 from @t where a.dgoodsid=mgoodsid)
set @i=@i-1
end
update @t set DAMT=DQTY*DPRICE where DAMT is null
return
end
go
select * from fn_bom('a001')
/*
MGOODSID MQTY DGOODSID ITEMNO DQTY DPRICE DAMT lvl
-------------------- --------------------------------------- -------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- -----------
A001 100.00 B001 1.00 100.00 3.00 300.00 0
A001 100.00 C001 2.00 100.00 1.00 100.00 0
A001 100.00 D001 3.00 100.00 1.00 100.00 0
B001 100.00 E001 1.00 100.00 1.00 100.00 1
B001 100.00 F001 2.00 100.00 2.00 200.00 1
F001 100.00 G001 1.00 100.00 1.00 100.00 2
F001 100.00 J001 2.00 100.00 1.00 100.00 2(7 行受影响)
*/--计算成本存储过程
If not object_id('[Sp_BOM]') is null
Drop proc [Sp_BOM]
Go
Create proc Sp_BOM(@GOODSID varchar(20))
as
select MGOODSID,MQTY,DGOODSID ,DQTY,DPRICE,DAMT
from fn_bom(@GOODSID)
where MGOODSID=@GOODSID
GO--调用过程
exec [Sp_BOM] 'B001'
/*
MGOODSID MQTY DGOODSID DQTY DPRICE DAMT
-------------------- --------------------------------------- -------------------- --------------------------------------- --------------------------------------- ---------------------------------------
B001 100.00 E001 100.00 1.00 100.00
B001 100.00 F001 100.00 2.00 200.00(2 行受影响)
*/
exec [Sp_BOM] 'A001'
/*
MGOODSID MQTY DGOODSID DQTY DPRICE DAMT
-------------------- --------------------------------------- -------------------- --------------------------------------- --------------------------------------- ---------------------------------------
A001 100.00 B001 100.00 3.00 300.00
A001 100.00 C001 100.00 1.00 100.00
A001 100.00 D001 100.00 1.00 100.00(3 行受影响)
*/
还有个不情之请，因为我们是做化工的，所以在BOM投料时在子件里面同一原料可以同时出现的例如：
做A半成品 100%
1/ 投入 B 30% 、D 50% 快速分散30分钟
2/再投入 B 20%研磨QC
哪么做成的BOM是这样的（BOM体现投料顺序及比例，所以不能合并B为70%）
A 100
B30 D50 B20在成本计算当中以上程序就会有点问题了：
exec [Sp_BOM] 'AV267' ---成本单价 5278.9/100=52.78 ----这是半成品
AV267 100.00 DR001 20.00 9.24 184.80
AV267 100.00 DR002 39.50 7.35 290.33
AV267 100.00 AR439 .50 47.38 23.69
AV267 100.00 DR105 40.00 13.39 535.60
AV267 100.00 DR001 20.00 9.24 184.80
AV267 100.00 DR002 39.50 7.35 290.33
AV267 100.00 AR439 .50 47.38 23.69
AV267 100.00 DR193 40.00 13.39 535.60
AV267 100.00 DR001 20.00 9.24 184.80
AV267 100.00 DR002 39.50 7.35 290.33
AV267 100.00 AR439 .50 47.38 23.69
AV267 100.00 DR138 40.00 13.18 527.20
AV267 100.00 DR001 20.00 9.24 184.80
AV267 100.00 DR002 39.50 7.35 290.33
AV267 100.00 AR439 .50 47.38 23.69
AV267 100.00 DR170 40.00 16.48 659.20
AV267 100.00 DR001 20.00 9.24 184.80
AV267 100.00 DR002 39.50 7.35 290.33
AV267 100.00 AR439 .50 47.38 23.69
AV267 100.00 DR138 40.00 13.18 527.20exec [Sp_BOM] 'AV612' ----这里出现两次的AV167 为什么这里单价不是52.78
----这是半成品
AV612 100.00 AV267 40.00 105.58 10558.44 ----105.58??
AV612 100.00 AR471 20.00 46.35 927.00
AV612 100.00 AV267 40.00 105.58 10558.44 ----105.58??
exec [Sp_BOM] 'GA2967' ----这才是成品
GA2967 100.00 AV18 32.00 44.79 4479.26
GA2967 100.00 AV612 6.00 220.44 22043.88
GA2967 100.00 AV168 10.00 32.18 3218.08
GA2967 100.00 PA3005 15.00 79.06 7905.76
GA2967 100.00 PA3014 30.00 95.49 9549.13
GA2967 100.00 . .00 .00 .00
GA2967 100.00 DR019 5.00 10.40 52.00
哈哈，好长的回复啊
我手动添加过数据了，87怎么不对，你看下原始数据
另外说下，所谓的Mqty，即父栏位数量，都设为1，是比较合理的，子数量再考虑相应倍率
引用 54 楼 sankyqiu 的回复:
playwarcraft兄在吗？这里能不能再帮忙处理一下，这个方法也不错呀！
p_id pqty tmp_id cqty dprice DAM
---------- ----------- ---------- ----------- ----------- -----------
A001 1 B001 1 87 87 -----这里不正确呀!
A001 1 C001 2 5 10
A001 1 D001 1 5 5*/GOdroptable bomdropproc
哈哈，好长的回复啊
我手动添加过数据了，87怎么不对，你看下原始数据
另外说下，所谓的Mqty，即父栏位数量，都设为1，是比较合理的，子数量再考虑相应倍率谢谢！
以下是在我的数据库里面运行的
exec dbo.usp_bom 'f001' 最下层正确
P_ID PQTY TMP_ID CQTY DPRICE DAM
F001 100.0 G001 100.0 1.0 100.0
F001 100.0 J001 100.0 1.0 100.0
exec dbo.usp_bom 'b001' ----中间层 F001 DPRICE 为0? DAM 2百万?
P_ID PQTY TMP_ID CQTY DPRICE DAM
B001 100.0 E001 100.0 1.0 100.0
B001 100.0 F001 100.0 0.0 2000000.0 ----这里有点小问题
exec dbo.usp_bom 'A001'
P_ID PQTY TMP_ID CQTY DPRICE DAM
A001 100.0 B001 100.0 0.0 3000000.0 ------ 0.0
A001 100.0 C001 100.0 1.0 100.0
A001 100.0 D001 100.0 1.0 100.0
Drop table [BOM]
Go
Create table [BOM]([BILLID] int,[MGOODSID] nvarchar(4),[MQTY] decimal(18,2),[DGOODSID] nvarchar(4),[ITEMNO] decimal(18,2),[DQTY] decimal(18,2),[DPRICE] decimal(18,2))
Insert BOM
Select 584,'A001',100.00,'B001',1.00,100.00,1 union all
Select 584,'A001',100.00,'C001',2,100,1.00 union all
Select 584,'A001',100.00,'D001',3,100,1 union all
Select 584,'A001',100.00,'B001',1.00,40.00,1 union all --增加一列
Select 588,'B001',100.00,'E001',1,100,1 union all
Select 588,'B001',100.00,'F001',2,100,1 union all
Select 560,'F001',100.00,'G001',1,100,1 union all
Select 560,'F001',100.00,'J001',2,100,1 Go
--Select * from BOM-->SQL查询如下:If not object_id('[Fn_BOM]') is null
Drop function [Fn_BOM]
Go
Create Function Fn_BOM(@MGoodsID varchar(20))
returns @t table(MGOODSID varchar(20),MQTY dec(18,2),DGOODSID varchar(20),ITEMNO dec(18,2),DQTY dec(18,2),DPRICE dec(18,2),DAMT dec(18,2),lvl int)
as
begin
declare @lvl int
set @lvl=0
insert @t select MGOODSID,MQTY,DGOODSID,ITEMNO,DQTY,DPRICE,NULL,@lvl from BOM where MGoodsID=@MGoodsID
while @@rowcount>0
begin
set @lvl=@lvl+1
insert @t
select a.MGOODSID,a.MQTY,a.DGOODSID,a.ITEMNO,a.DQTY,a.DPRICE,a.DQTY*a.DPRICE,@lvl
from BOM a,@t b
where a.MGoodsID=b.DGoodsID
and b.lvl=@lvl-1
end
declare @i int
select @i=max(lvl) from @t t where exists(select 1 from @t where t.dgoodsid=mgoodsid)
while @i>=0
begin
update a set
dprice=(select sum(damt) from @t where a.DGOODSID=MGOODSID)/(select sum(mqty) from @t where DGOODSID=a.DGOODSID),
damt=(select sum(damt) from @t where a.DGOODSID=MGOODSID)/(select count(1) from @t where DGOODSID=a.DGOODSID)
from @t a
where lvl=@i
and exists(select 1 from @t where a.dgoodsid=mgoodsid)
set @i=@i-1
end
update @t set DAMT=DQTY*DPRICE where DAMT is null
return
end
go
select * from fn_bom('a001')
/*
MGOODSID MQTY DGOODSID ITEMNO DQTY DPRICE DAMT lvl
A001 100.00 B001 1.00 100.00 3.00 300.00 0
A001 100.00 C001 2.00 100.00 1.00 100.00 0
A001 100.00 D001 3.00 100.00 1.00 100.00 0
A001 100.00 B001 1.00 40.00 3.00 300.00 0
B001 100.00 E001 1.00 100.00 1.00 100.00 1
B001 100.00 E001 1.00 100.00 1.00 100.00 1
B001 100.00 F001 2.00 100.00 2.00 200.00 1
B001 100.00 F001 2.00 100.00 2.00 200.00 1
F001 100.00 G001 1.00 100.00 1.00 100.00 2
F001 100.00 G001 1.00 100.00 1.00 100.00 2
F001 100.00 J001 2.00 100.00 1.00 100.00 2
F001 100.00 J001 2.00 100.00 1.00 100.00 2
*/--计算成本存储过程
If not object_id('[Sp_BOM]') is null
Drop proc [Sp_BOM]
Go
Create proc Sp_BOM(@GOODSID varchar(20))
as
select MGOODSID,MQTY,DGOODSID ,DQTY,DPRICE,DAMT
from fn_bom(@GOODSID)
where MGOODSID=@GOODSID
GO--调用过程
exec [Sp_BOM] 'B001'
/*
MGOODSID MQTY DGOODSID DQTY DPRICE DAMT
B001 100.00 E001 100.00 1.00 100.00
B001 100.00 F001 100.00 2.00 200.00
*/
exec [Sp_BOM] 'A001'
/*
MGOODSID MQTY DGOODSID DQTY DPRICE DAMT
A001 100.00 B001 100.00 3.00 300.00
A001 100.00 C001 100.00 1.00 100.00
A001 100.00 D001 100.00 1.00 100.00
A001 100.00 B001 40.00 3.00 300.00
*/
你看算的过程中有用到 cqty
现在你的cqty是100， 当然会多出很多倍
Drop function [Fn_BOM]
Go
Create Function Fn_BOM(@MGoodsID varchar(20))
returns @t table(MGOODSID varchar(20),MQTY dec(18,2),DGOODSID varchar(20),ITEMNO dec(18,2),DQTY dec(18,2),DPRICE dec(18,2),DAMT dec(18,2),lvl int)
as
begin
declare @lvl int
set @lvl=0
insert @t select MGOODSID,MQTY,DGOODSID,ITEMNO,DQTY,DPRICE,NULL,@lvl from BOM where MGoodsID=@MGoodsID
while @@rowcount>0
begin
set @lvl=@lvl+1
insert @t
select a.MGOODSID,a.MQTY,a.DGOODSID,a.ITEMNO,a.DQTY,a.DPRICE,a.DQTY*a.DPRICE,@lvl
from BOM a,@t b
where a.MGoodsID=b.DGoodsID
and b.lvl=@lvl-1
end
declare @i int
select @i=max(lvl) from @t t where exists(select 1 from @t where t.dgoodsid=mgoodsid)
while @i>=0
begin
update a set
dprice=(select sum(damt) from @t where a.DGOODSID=MGOODSID)/(select sum(mqty) from @t where DGOODSID=a.DGOODSID),
damt=(select sum(damt) from @t where a.DGOODSID=MGOODSID)/(select count(1) from @t where DGOODSID=a.DGOODSID) from @t a
where lvl=@i
and exists(select 1 from @t where a.dgoodsid=mgoodsid)
set @i=@i-1
end
update @t set DAMT=DQTY*DPRICE where DAMT is null
return
end
go
改动红字部份
因为以下地址给160分，但系统不给我出160分所以只出100
http://topic.csdn.net/u/20090830/20/3f4f3d9e-9ed4-4bdc-a06b-469a39478ce4.html
If not object_id('[FN_BOMCOST]') is null
Drop FUNCTION [FN_BOMCOST]
Go
Create FUNCTION [FN_BOMCOST](@GOODSID varchar(20))
returns table
as
return(
select MGOODSID,MQTY,DGOODSID ,DQTY,DPRICE,DAMT
from fn_bom(@GOODSID)
where MGOODSID=@GOODSID
)
GOselect * from [FN_BOMCOST]('B001')
/*
MGOODSID MQTY DGOODSID DQTY DPRICE DAMT
B001 100.00 E001 100.00 1.00 100.00
B001 100.00 F001 100.00 2.00 200.00
*/