根据BOM重算物料单价

2004-07-23 08:59:12 388 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

制造 MES 系统之BOM物料清单详细解析（附解决方案）物料清单（BOM，Bill of Materials）是制造生产企业的核心枢纽。它以层级分明的产品结构树，精准记录生产所需的物料、零部件等信息。从产品设计起，BOM 就将设计理念具象化，明确各层级构成，为后续生产打基础。在实际生产中，BOM 为采购部门提供采购清单，保障原材料供应；为生产部门规划装配路径，助力产品组装；为质量控制部门提供检验依据，把控各生产环节质量。BOM 贯穿产品全生命周期，从设计研发到售后服务，都提供精准指导与支持。它是企业高效生产、保障质量的基石，更是提升核心竞争力的关键。阅读详情

品名表(f_product_name)
material_name varchar(20) --物料品名
unit_price numeric(12,4) --单价
BOM清单(tabMaterialBom)
material_name varchar(20) --物料品名
f_no numeric(4) --序号(基于物料品名递增)
son_material_name varchar(20) –子项品名
son_num numeric(10,2) --子项用量
(其中f_product_name.material_name为主键，tabMaterialBom.material_name与tabMaterialBom.f_no为联合主键，并且物料品名与子项品名都存在于品名表中)
要求根据BOM树叶节点的单价和用量，采用加权平均法重算非叶节点的单价。求各位帮忙，谢谢!!!!!!!!!!!(在线等)

...全文

数据仓库实践：使用 SQL 计算材料BOM成本单价初始化时使用核价和成本单间仍然不能完全覆盖企业内使用的所有的材料，尤其是BOM结构复杂、材料料号过多的情况下。所以一般情况下，企业会使用BOM关系，将材料的单价汇总计算到上阶材料。数据仓库实践：使用SQL汇总BOM数据分析维度 http://t.csdnimg.cn/gZ1pS部分 dim_bom 结构。阅读详情

shenen

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

shenen 2004-07-27

打赏
举报

謝謝各位朋友的幫忙。

shenen 2004-07-24

打赏
举报

哪位朋友想清楚了，我還是沒有頭緒啊

shenen 2004-07-24

打赏
举报

我想在zjcxc(邹建)程序的基礎上改進，可是我不會改，並不是我不想自己動手，只是能力不足

sankis 2004-07-24

打赏
举报

你为什么不试试用程序加存储过程一起算啊？

shenen 2004-07-24

打赏
举报

我都想了三天了，還沒想出來

zjcxc 元老 2004-07-23

打赏
举报

--测试楼主的最新数据

--测试数据
create table f_product_name( --品名#t
material_name varchar(20), --物料品名
unit_price numeric(12,4), --单价
constraint PK_f_product_name primary key(material_name)
)
insert f_product_name
select 'A',1
union all select 'B',5
union all select 'C',4
union all select 'D',6
union all select 'E',2

create table tabMaterialBom( --BOM清单
material_name varchar(20), --物料品名
f_no numeric(4), --序号(基于物料品名递增)
son_material_name varchar(20), --子项品名
son_num numeric(10,2), --子项用量
constraint PK_tabMaterialBom primary key(material_name,f_no)
)
insert tabMaterialBom
select 'B',1,'A',10 --1*10
union all select 'B',2,'E', 5 --2*5
union all select 'C',1,'B', 2 --20*2
--union all select 'C',1,'B', 5 --20*5
--union all select 'C',2,'D', 5 --105*5
--union all select 'D',1,'A', 5 --1*5
--union all select 'D',2,'B', 5 --20*5
go

--计算处理的存储过程
create proc p_calc
as
--生成处理的临时#t
select a.material_name,a.son_material_name,a.son_num
,sum_unit_price=b.unit_price*a.son_num
into #t
from tabMaterialBom a
join f_product_name b
on a.son_material_name=b.material_name

--分级计算
update #t set sum_unit_price=-1
from #t a
where exists(
select 1 from #t where material_name=a.son_material_name)
while @@rowcount>0
update #t set sum_unit_price=a.son_num*isnull(b.sum_unit_price,0)
from #t a join(
select material_name,sum_unit_price=sum(sum_unit_price) from #t
where sum_unit_price<>0 group by material_name
)b on a.son_material_name=b.material_name
where a.sum_unit_price=-1 and not exists(
select 1 from #t where material_name=a.son_material_name and sum_unit_price=-1)

--更新单价
update a set unit_price=b.sum_unit_price
from f_product_name a join(
select material_name,sum_unit_price=sum(sum_unit_price)
from #t
group by material_name
)b on a.material_name=b.material_name
go

--调用存储过程进行计算
exec p_calc

--显示计算结果
select * from f_product_name
go

--删除测试
drop table f_product_name,tabMaterialBom
drop proc p_calc

/*--测试结果

material_name unit_price
-------------------- --------------
A 1.0000
B 20.0000
C 40.0000
D 6.0000
E 2.0000

（所影响的行数为 5 行）
--*/

zjcxc 元老 2004-07-23

打赏
举报

--测试

--测试数据
create table f_product_name( --品名#t
material_name varchar(20), --物料品名
unit_price numeric(12,4), --单价
constraint PK_f_product_name primary key(material_name)
)
insert f_product_name
select 'A',1
union all select 'B',5
union all select 'C',4
union all select 'D',6
union all select 'E',2

create table tabMaterialBom( --BOM清单
material_name varchar(20), --物料品名
f_no numeric(4), --序号(基于物料品名递增)
son_material_name varchar(20), --子项品名
son_num numeric(10,2), --子项用量
constraint PK_tabMaterialBom primary key(material_name,f_no)
)
insert tabMaterialBom
select 'B',1,'A',10 --1*10
union all select 'B',2,'E', 5 --2*5
union all select 'C',1,'B', 5 --20*5
union all select 'C',2,'D', 5 --105*5
union all select 'D',1,'A', 5 --1*5
union all select 'D',2,'B', 5 --20*5
go

--计算处理的存储过程
create proc p_calc
as
--生成处理的临时#t
select a.material_name,a.son_material_name,a.son_num
,sum_unit_price=b.unit_price*a.son_num
into #t
from tabMaterialBom a
join f_product_name b
on a.son_material_name=b.material_name

--分级计算
update #t set sum_unit_price=-1
from #t a
where exists(
select 1 from #t where material_name=a.son_material_name)
while @@rowcount>0
update #t set sum_unit_price=a.son_num*isnull(b.sum_unit_price,0)
from #t a join(
select material_name,sum_unit_price=sum(sum_unit_price) from #t
where sum_unit_price<>0 group by material_name
)b on a.son_material_name=b.material_name
where a.sum_unit_price=-1 and not exists(
select 1 from #t where material_name=a.son_material_name and sum_unit_price=-1)

--更新单价
update a set unit_price=b.sum_unit_price
from f_product_name a join(
select material_name,sum_unit_price=sum(sum_unit_price)
from #t
group by material_name
)b on a.material_name=b.material_name
go

--调用存储过程进行计算
exec p_calc

--显示计算结果
select * from f_product_name
go

--删除测试
drop table f_product_name,tabMaterialBom
drop proc p_calc

/*--测试结果

material_name unit_price
-------------------- --------------
A 1.0000
B 20.0000
C 625.0000
D 105.0000
E 2.0000

（所影响的行数为 5 行）
--*/

zjcxc 元老 2004-07-23

打赏
举报

--上面的存储过程考虑欠妥,算错了

--计算处理的存储过程
create proc p_calc
as
--生成处理的临时#t
select a.material_name,a.son_material_name,a.son_num
,sum_unit_price=b.unit_price*a.son_num
into #t
from tabMaterialBom a
join f_product_name b
on a.son_material_name=b.material_name

--分级计算
update #t set sum_unit_price=-1
from #t a
where exists(
select 1 from #t where material_name=a.son_material_name)
while @@rowcount>0
update #t set sum_unit_price=a.son_num*isnull(b.sum_unit_price,0)
from #t a join(
select material_name,sum_unit_price=sum(sum_unit_price) from #t
where sum_unit_price<>0 group by material_name
)b on a.son_material_name=b.material_name
where a.sum_unit_price=-1 and not exists(
select 1 from #t where material_name=a.son_material_name and sum_unit_price=-1)

--更新单价
update a set unit_price=b.sum_unit_price
from f_product_name a join(
select material_name,sum_unit_price=sum(sum_unit_price)
from #t
group by material_name
)b on a.material_name=b.material_name
go

shenen 2004-07-23

打赏
举报

感謝各位的熱心回答，先自我批評一下，我沒有把問題說清楚。接著說：(例如)
BOM清单
物料品名序号子项品名子项数量
B 1 A 10
B 2 E 5
C 1 B 2
這時就不好算了，所以每種物料必須從最底層算起。

dechon 2004-07-23

打赏
举报

如果只是一级的关系比较好处理，如果是多级的话只能使用递归了。

zjcxc 元老 2004-07-23

打赏
举报

--计算处理的存储过程
create proc p_calc
as
--生成处理的临时#t
select a.material_name,a.son_material_name
,unit_price=a.son_num*b.unit_price
,sum_unit_price=cast(-1 as numeric(10,2))
into #t
from tabMaterialBom a
join f_product_name b
on a.son_material_name=b.material_name

--分级计算
update #t set sum_unit_price=unit_price
from #t a
where not exists(
select 1 from #t where material_name=a.son_material_name)
while @@rowcount>0
update #t set sum_unit_price=isnull(a.unit_price,0)+isnull(b.sum_unit_price,0)
from #t a join(
select material_name,sum_unit_price=sum(sum_unit_price) from #t
where sum_unit_price<>0 group by material_name
)b on a.son_material_name=b.material_name
where a.sum_unit_price=-1 and not exists(
select 1 from #t where material_name=a.son_material_name and sum_unit_price=-1)

--更新单价
update a set unit_price=b.sum_unit_price
from f_product_name a join(
select material_name,sum_unit_price=sum(sum_unit_price)
from #t
group by material_name
)b on a.material_name=b.material_name
go

zheninchangjiang 2004-07-23

打赏
举报

递归算，mark

loverpyh 2004-07-23

打赏
举报

select 物料品名,sum(Price) Price From (Select a.物料品名,a.子项数量*isnull(b.单价,0) Price From BOM清单 a left join 品名表 b on a.子项品名=b.物料品名) c group by c.物料品名

青团子 2004-07-23

打赏
举报

zjcxc 元老 2004-07-23

打赏
举报

不是平均,是累计

shenen 2004-07-23

打赏
举报

是B物料的单价应为20，不是E

shenen 2004-07-23

打赏
举报

>>采用加权平均法重算非叶节点的单价
这句话错了，如上例：计算出的E物料的单价应该为20

shenen 2004-07-23

打赏
举报

品名表
物料品名单价
A 1
B 5
C 4
D 6
E 2
--------------------------------------------
BOM清单
物料品名序号子项品名子项数量
B 1 A 10
B 2 E 5
--------------
这组数据表示：1个B物料是由10个A物料和5个E物料做成的
由于A和E物料单价的变动，现在要重算B物料的单价
(不知我说明白没有？)

zjcxc 元老 2004-07-23

打赏
举报

举数据说明,这样更方便理解及测试.

zjcxc 元老 2004-07-23

打赏
举报

自己慢慢想清楚,在那个基础上加吧

不要什么都让别人动手.

加载更多回复（1）

根据BOM重算物料单价

相关推荐