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
不用寫存儲過程,2008以上是否可以用CTE來展BOM,如何可以，該如何寫,以上隨便做一條測試數據，得到如下結果!
母版料號 了版料號 用量 Bom階層 是否為低階物料
A B 1 1 N
A C 2 1 Y
A E 9 2 N
A G 6 2 Y
A H 8 2 Y
A F 18 3 Y
SQL
------解决方案--------------------
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 '用量',