with as 查询树递归的用法

 

with bomtmp as
(
select aMD001, aMD003, aMD006, aMD007, aMD008, 1 kind, aMB025 from BOMaMD join INVaMBTEM on aMB001 = aMD003 where aMD001 = '101101180169'
union all
select a.aMD001, a.aMD003, a.aMD006, a.aMD007, a.aMD008, b.kind +1 kind , c.aMB025
from BOMaMD a
join bomtmp b on b.aMD003 = a.aMD001
join INVaMB c on c.aMB001 = a.aMD003
where b.aMB025 not in ('M', 'S','P')
)
select aMD001, aMD003, aMD006, aMD007, aMD008, kind, aMB025 from bomtmp order by kind ,aMD001;

-- 验证 数据
-- select aMB001, aMB025 from INVaMB where aMB001 ='331101180109'

--select * from dbo.MRP_BOaMD('101101180169',1,1,'101101180169','WO')