突然想問個問個關於CTE的問題

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

4 个解决方案

#1

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 '用量',
       lv 'Bom階層',
       case when not exists(select 1 from #bom e
                            where e.mother_no=t.child_no) then 'Y' else 'N' end '是否為低階物料'
  from t

/*
母版料號       子版料號       用量          Bom階層       是否為低階物料
---------- ---------- ----------- ----------- -------
A          B          3           1           N
A          C          2           1           Y
A          E          9           2           N
A          G          6           2           Y
A          H          12          2           Y
A          F          18          3           Y

(6 row(s) affected)
*/

#2

WITH a1 AS
(
SELECT mother_no,mother_no m2,child_no,bo_qty,1 sn FROM #bom WHERE mother_no='A'
UNION ALL
SELECT a1.mother_no,a.mother_no,a.child_no,CAST(a1.bo_qty*a.bo_qty AS DECIMAL(20,4)),a1.sn+1
FROM #bom a
INNER JOIN a1 ON a.mother_no=a1.child_no
)
SELECT mother_no,child_no,bo_qty,sn,
CASE WHEN EXISTS(SELECT 1 FROM a1 WHERE m2=a.child_no) THEN 'N' ELSE 'Y' end
FROM a1 a

#3

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 
;with sel as(
select mother_no,child_no,bo_qty,1 as lev
from #bom where mother_no ='A'
union all
select t.mother_no,t.child_no,t.bo_qty,lev+1
from #bom t join sel s
on t.mother_no=s.child_no
)
select *,case when lev=1 then 'N' else 'Y' end as issub from sel

#4

分數比較少，別介意啊！