日期:2014-05-17 浏览次数:20518 次
create table #bom(mother_no varchar(10),child_no varchar(10),bo_qty decimal(20,4))--母料號,子料號,用量
insert into #bom (mother_no,child_no,bo_qty)
select 'A','B',3 union all
select 'A','C',2 union all
select 'B','E',3 union all
select 'B','G',2 union all
select 'B','H',4 union all
select 'E','F',2
create table #bom(mother_no varchar(10),
child_no varchar(10),
bo_qty int)
insert into #bom(mother_no,child_no,bo_qty)
select 'A','B',3 union all
select 'A','C',2 union all
select 'B','E',3 union all
select 'B','G',2 union all
select 'B','H',4 union all
select 'E','F',2
with t as
(select a.mother_no,a.child_no,a.bo_qty,a.mother_no 'max_mother_no',1 'lv'
from #bom a
where not exists(select 1 from #bom b where b.child_no=a.mother_no)
union all
select d.mother_no,d.child_no,d.bo_qty*c.bo_qty,c.max_mother_no,c.lv+1 'lv'
from t c
inner join #bom d on c.child_no=d.mother_no
)
select max_mother_no '母版料號',
child_no '子版料號',
bo_qty '用量',