根据BOM重算物料单价

[shenen]

品名表(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树叶节点的单价和用量，采用加权平均法重算非叶节点的单价。求各位帮忙，谢谢!!!!!!!!!!!(在线等)

[zjcxc]

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

不要什么都让别人动手.

[zjcxc]

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

[shenen]

品名表
物料品名 单价
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物料的单价
(不知我说明白没有？)

[shenen]

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

[shenen]

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

[zjcxc]

不是平均,是累计

[青团子]

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.物料品名

[loverpyh]

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.物料品名

[zheninchangjiang]

递归算，mark

[zjcxc]

--计算处理的存储过程
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

[dechon]

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

[shenen]

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

[zjcxc]

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

--计算处理的存储过程
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

[zjcxc]

--测试

--测试数据
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]

--测试楼主的最新数据

--测试数据
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 行）
--*/

[shenen]

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

[sankis]

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

[shenen]

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

[shenen]

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

[shenen]

謝謝各位朋友的幫忙。