oracle bom递归查询,Oralce 使用递归方式获取BOM树显示结构

WITH TB (L_LEVEL,PARENT_CODE,SON_CODE,FBOMID) --要有列名

AS(

SELECT 0 AS L_LEVEL,FPRODUCTNO AS PARENT_CODE,FPRODUCTNO AS SON_CODE,FID AS FBOMID FROM T_BOM WHERE fproductno=‘R00127‘ AND fflag=1

union all

select TB.L_LEVEL+1 AS L_LEVEL,A.PARENT_CODE,A.SON_CODE,(select MAX(fid) from t_bom where fproductno=A.SON_CODE AND fflag=1 ) AS FBOMID

from PRODUCT_RELATION A inner join TB on A.PARENT_CODE=TB.SON_CODE AND A.FID=FBOMID  --必须关联，不能直接where A.FID=TB.FBOMID

)

SELECT * FROM TB;

原文：https://www.cnblogs.com/Bokeyan/p/12743438.html