SQL数据库父子树数据维护：仅返回完整树节点的技术问询

Got it, let's break this down. You’ve got a parent-child tree structure spread across multiple SQL tables, and you’re already using recursive CTEs to query the data successfully. Now you need to do maintenance to validate data correctness, specifically returning only complete tree nodes (I’m assuming this means nodes that form fully intact subtrees or complete root-to-leaf paths—if my interpretation is off, feel free to clarify the exact definition!).

Below are practical, SQL-based solutions tailored to your needs:

This approach uses a recursive CTE to traverse the tree while validating that every node has a valid parent (except root nodes) and that all expected descendants exist. We’ll filter out any subtrees with broken links.

Assuming your core table is Filters with columns:

• FilterID: Unique node ID (primary key)
• ParentFilterID: Parent node ID (NULL/0 for root nodes)
• Other business-specific fields (e.g., FilterName)

WITH RecursiveTree AS (
    -- Anchor member: Start with root nodes
    SELECT 
        FilterID,
        ParentFilterID,
        FilterName,
        1 AS NodeLevel,
        FilterID AS RootNodeID,
        -- Mark root nodes as initially complete (since they have no parent)
        CAST(1 AS BIT) IsSubtreeComplete
    FROM Filters
    WHERE ParentFilterID IS NULL -- Adjust this to match your root node condition

    UNION ALL

    -- Recursive member: Traverse child nodes and validate integrity
    SELECT 
        f.FilterID,
        f.ParentFilterID,
        f.FilterName,
        rt.NodeLevel + 1,
        rt.RootNodeID,
        -- Check if current node has a valid parent (exists in the source table)
        CAST(CASE WHEN EXISTS(SELECT 1 FROM Filters WHERE FilterID = f.ParentFilterID) THEN 1 ELSE 0 END AS BIT)
    FROM Filters f
    INNER JOIN RecursiveTree rt ON f.ParentFilterID = rt.FilterID
)
-- Return only subtrees where every node is valid
SELECT *
FROM RecursiveTree rt
WHERE NOT EXISTS (
    SELECT 1 
    FROM RecursiveTree rt2 
    WHERE rt2.RootNodeID = rt.RootNodeID 
      AND rt2.IsSubtreeComplete = 0
);

If your goal is to extract only the full paths from root nodes down to leaf nodes (nodes with no children), use this variant to mark and filter leaf nodes:

WITH RecursiveTree AS (
    -- Anchor member: Root nodes
    SELECT 
        FilterID,
        ParentFilterID,
        FilterName,
        1 AS NodeLevel,
        FilterID AS RootNodeID,
        -- Mark if node is a leaf (no child nodes)
        CAST(CASE WHEN NOT EXISTS(SELECT 1 FROM Filters WHERE ParentFilterID = f.FilterID) THEN 1 ELSE 0 END AS BIT) IsLeaf,
        -- Build a human-readable path string for validation
        CAST(FilterName AS VARCHAR(MAX)) FullPath
    FROM Filters f
    WHERE ParentFilterID IS NULL

    UNION ALL

    -- Recursive member: Traverse children and update path/leaf status
    SELECT 
        f.FilterID,
        f.ParentFilterID,
        f.FilterName,
        rt.NodeLevel + 1,
        rt.RootNodeID,
        CAST(CASE WHEN NOT EXISTS(SELECT 1 FROM Filters WHERE ParentFilterID = f.FilterID) THEN 1 ELSE 0 END AS BIT) IsLeaf,
        rt.FullPath + ' > ' + f.FilterName AS FullPath
    FROM Filters f
    INNER JOIN RecursiveTree rt ON f.ParentFilterID = rt.FilterID
)
-- Return only full root-to-leaf paths
SELECT *
FROM RecursiveTree
WHERE IsLeaf = 1;

For thorough maintenance, add these checks to catch common tree data errors:

• Find orphaned nodes (non-root nodes with no valid parent):

  SELECT *
  FROM Filters
  WHERE ParentFilterID IS NOT NULL
    AND NOT EXISTS(SELECT 1 FROM Filters WHERE FilterID = ParentFilterID);
  

• Find nodes marked as parents but with no children (if your business rules require them to have children):

  SELECT *
  FROM Filters
  WHERE FilterID IN (SELECT DISTINCT ParentFilterID FROM Filters)
    AND NOT EXISTS(SELECT 1 FROM Filters WHERE ParentFilterID = FilterID);
  

• Detect circular references (e.g., Node A is parent of Node B, Node B is parent of Node A):

  WITH RecursiveTree AS (
      SELECT 
          FilterID,
          ParentFilterID,
          CAST(',' + CAST(FilterID AS VARCHAR) + ',' AS VARCHAR(MAX)) VisitedNodes,
          CAST(0 AS BIT) HasCycle
      FROM Filters
      WHERE ParentFilterID IS NULL
  
      UNION ALL
  
      SELECT 
          f.FilterID,
          f.ParentFilterID,
          rt.VisitedNodes + CAST(f.FilterID AS VARCHAR) + ',',
          CAST(CASE WHEN rt.VisitedNodes LIKE '%,' + CAST(f.FilterID AS VARCHAR) + ',%' THEN 1 ELSE 0 END AS BIT) HasCycle
      FROM Filters f
      INNER JOIN RecursiveTree rt ON f.ParentFilterID = rt.FilterID
      WHERE rt.HasCycle = 0
  )
  SELECT *
  FROM RecursiveTree
  WHERE HasCycle = 1;
  

内容的提问来源于stack exchange，提问作者Brad