How to prune dead branches in a hierarchical data structure

We have a table that has an int NodeID column for primary key and a self join to the int ParentID column and a varchar(25) Name column.

We need a query that will delete all dead branches for a given set of leaf nodes (or bottom level nodes). For Example, if given a set of nodes like below, if the NodeID 4 is passed in, Node 4 should be deleted and since Node 2 no longer has children, it should be deleted. Node 1 still has a child (Node 6) so the process would stop there.

                       [0] ROOT
                      /         
            [1] Node 1          [3] Node 3
            /                          
      [2] Node 2     [6] Node 6      [5] Node 5
      /          
 [4] Node 4

I have a solution using a cursor, but I avoid cursors whenever possible and would much prefer a batch means of accomplishing this. Is there a way to do this with CTE? or any other means?

The CTE below returns all the parents. I've made a couple attempt to change the last select to delete where the node only includes a single child or no children. Nothing I've tried along those lines executes.

Any approach will have to have some means of walking up the hierarchy and deleting nodes without children.

Any other ideas or approaches are welcome.

WITH nAncestry (ParentID, NodeID, Name, AncestryID)
AS
(
    SELECT n.ParentID, n.NodeID, n.Name, 0 AS AncestryID
    FROM Node AS n
    WHERE n.NodeID=@nodeID

    UNION ALL

    SELECT n.ParentID, n.NodeID, n.Name, AncestryID + 1
    FROM Node AS n
    INNER JOIN nAncestry as a 
        ON a.ParentID = n.NodeID
)

SELECT ParentID, NodeID, Name
FROM nAncestry
WHERE NodeID <>0
ORDER BY  AncestryID DESC

链接地址: http://www.djcxy.com/p/60226.html

上一篇: 使用SQL server hierarchyid如何在祖先级别聚合叶节点值

下一篇: 如何修剪分层数据结构中的死分支