基于递归CTE生成指定内容ID的父级URL路径

Got it, let's fix this. You want the URL path of only the parent nodes for a given ContentID, excluding the current node itself—since your existing recursive CTEs were including the full path with the target item. Here's how to adjust the approach:

Key Idea #

Instead of starting the recursion at the target node, start directly at its immediate parent. Then traverse upward to collect all ancestor nodes, format their titles into URL-friendly strings (using your existing space-replacement function), and stitch them together in the correct hierarchy.

SQL Query (SQL Server Example) #

Assuming your content table is named Content, and your space-replacement function is dbo.FormatForUrl (swap this with your actual function name):

DECLARE @TargetContentID INT = 5; -- Replace with your desired ContentID

WITH ParentHierarchy AS (
    -- Anchor: Start with the immediate parent of the target node
    SELECT 
        ContentID,
        Parent,
        Title,
        1 AS HierarchyLevel
    FROM 
        Content
    WHERE 
        ContentID = (SELECT Parent FROM Content WHERE ContentID = @TargetContentID)
        -- Skip if target is a top-level node (Parent=0)
        AND (SELECT Parent FROM Content WHERE ContentID = @TargetContentID) <> 0

    UNION ALL

    -- Recursive step: Move up to each parent's parent
    SELECT 
        p.ContentID,
        p.Parent,
        p.Title,
        ph.HierarchyLevel + 1 AS HierarchyLevel
    FROM 
        Content p
    INNER JOIN 
        ParentHierarchy ph ON p.ContentID = ph.Parent
)

-- Generate the final URL path
SELECT 
    CASE 
        WHEN EXISTS(SELECT 1 FROM ParentHierarchy)
        THEN '/' + STRING_AGG(dbo.FormatForUrl(LOWER(Title)), '/') WITHIN GROUP (ORDER BY HierarchyLevel DESC) + '/'
        ELSE '/' -- Return root if no parents exist (top-level target)
    END AS ParentURLPath
FROM 
    ParentHierarchy;

Breakdown of the Query #

• Anchor CTE: We directly fetch the immediate parent of the target node. If the target is a top-level node (Parent=0), this returns no rows, which we handle later with the CASE statement.
• Recursive CTE: This climbs up the hierarchy, collecting each parent node until we reach the top-level (since there's no ContentID=0, the INNER JOIN will terminate the recursion automatically).
• HierarchyLevel: This field ensures we order the nodes from top-level parent to the immediate parent of the target, so the URL path is structured correctly (e.g., /another-page/child-of-another/ instead of /child-of-another/another-page/).
• STRING_AGG: Aggregates the URL-formatted titles into a single path string, wrapped in forward slashes to match standard URL structure.
• Edge Case Handling: If the target node has no parents (it's a top-level item), we return / (adjust this to an empty string if that's preferred).

Testing with Your Sample Data #

For @TargetContentID = 5, the query will:

• Collect nodes 4 (immediate parent) and 3 (parent of 4)
• Format their titles to child-of-another and another-page
• Stitch them into /another-page/child-of-another/, which matches your desired output.

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