突然想於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 11 N
A C 21 Y
A E 92 N
A G 62 Y
A H 82 Y
A F 183 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 '用量', 
 
       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)
*/

[解决办法]
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
[解决办法]

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