SQL中BOM递归查询语句

向下递归查询

WITH CTE AS (
-- 锚点：LT-W-00000001
SELECT bom_no,prd_no,zc_no,id_no,0 AS Level -- 层级：0 表示起始
FROM tf_bom
WHERE bom_no = 'LT-W-00000001->'
UNION ALL
-- 递归：查找下层
SELECT e.bom_no, e.prd_no,e.zc_no,e.id_no,eh.Level + 1
FROM tf_bom e
INNER JOIN CTE eh ON e.bom_no = eh.id_no
)
SELECT SPACE(Level * 2) + prd_no AS [料号],zc_no,Level AS [层级] FROM CTE