SQL Server树形结构获取所有的子节点

;WITH Tree AS
(
    -- 基础节点（起始节点）
    SELECT JobNum, TargetJobNum,1 as deep
    FROM Erp.JobProd
    WHERE JobNum = 'M000247'

    UNION ALL

    -- 递归查找子节点
    SELECT c.JobNum, c.TargetJobNum,p.deep+1 as deep
    FROM Erp.JobProd c
    INNER JOIN Tree p ON c.TargetJobNum = p.JobNum
)
SELECT *
FROM Tree;

;WITH Ancestors  AS
(
    -- 基础节点（起始节点）
    SELECT JobNum, TargetJobNum,0 as deep
    FROM Erp.JobProd
    WHERE JobNum = '000296'

    UNION ALL

    -- 递归查找子节点
    SELECT c.JobNum, c.TargetJobNum,p.deep+1 as deep
    FROM Erp.JobProd c
    INNER JOIN Ancestors  p ON c.JobNum = p.TargetJobNum
)


SELECT * FROM Ancestors OPTION (MAXRECURSION 0)