如何在SQL Server数据库中动态导航表外键关系的层次结构，以获取最终的父项？

WITH RecursiveCTE AS (
    -- Anchor member: 查询根节点
    SELECT 
        ChildTable.PrimaryKeyColumn AS Child,
        ParentTable.PrimaryKeyColumn AS Parent
    FROM 
        YourChildTable AS ChildTable
    INNER JOIN 
        YourParentTable AS ParentTable ON ChildTable.ForeignKeyColumn = ParentTable.PrimaryKeyColumn
    WHERE 
        ChildTable.PrimaryKeyColumn = 'YourChildPrimaryKeyValue'
    
    UNION ALL
    
    -- Recursive member: 递归查询子节点的父节点
    SELECT 
        ChildTable.PrimaryKeyColumn AS Child,
        ParentTable.PrimaryKeyColumn AS Parent
    FROM 
        YourChildTable AS ChildTable
    INNER JOIN 
        YourParentTable AS ParentTable ON ChildTable.ForeignKeyColumn = ParentTable.PrimaryKeyColumn
    INNER JOIN 
        RecursiveCTE AS Recursive ON ParentTable.PrimaryKeyColumn = Recursive.Child
)
-- 最终查询：获取最终的父项
SELECT 
    Parent
FROM 
    RecursiveCTE
WHERE 
    Child = 'YourChildPrimaryKeyValue'