用于获取SQL Server 2014中每条记录的所有父记录和子记录的SQL查询

-- 获取指定记录的所有父记录
WITH RecursiveCTE AS (
    SELECT ID, ParentID, Name
    FROM YourTable
    WHERE ID = @RecordID -- 替换为指定记录的ID
    UNION ALL
    SELECT t.ID, t.ParentID, t.Name
    FROM YourTable t
    INNER JOIN RecursiveCTE cte ON t.ID = cte.ParentID
)
SELECT ID, ParentID, Name
FROM RecursiveCTE

-- 获取指定记录的所有子记录
WITH RecursiveCTE AS (
    SELECT ID, ParentID, Name
    FROM YourTable
    WHERE ID = @RecordID -- 替换为指定记录的ID
    UNION ALL
    SELECT t.ID, t.ParentID, t.Name
    FROM YourTable t
    INNER JOIN RecursiveCTE cte ON t.ParentID = cte.ID
)
SELECT ID, ParentID, Name
FROM RecursiveCTE