SQL server 裡面用SQL達成遞迴

直接給程式
;with bomRecursive(MD001,MD003,Level,semiMaterial) as (
select  MD001,MD003,1,a.MD001 from BOMMD a
union all
select a.MD001,b.MD003,b.Level+1,b.semiMaterial
from  bomRecursive b inner join BOMMD a on a.MD003=b.MD001

)
select * from bomRecursive where MD001='1001120597'
order by Level desc ,MD003
option (MAXRECURSION 10)

解釋：（個人理解的方式）
bomRecursive<==遞迴用的函式名稱
 select  MD001,MD003,1,a.MD001 from BOMMD a<==第一階查詢
其中1代表第一階
 select a.MD001,b.MD003,b.Level+1,b.semiMaterial<==遞迴查詢查詢
b.level代表查詢到第幾階 ，要注意因為使用union，所以SQL欄位型態、欄位數量要一致

from  bomRecursive b inner join BOMMD a on a.MD003=b.MD001<==bomRecursive代表回傳的資料表值函式、可以join其他資料


 option (MAXRECURSION 10)<==代表最多查詢遞迴幾階，可以省略，預設1000