使用SQL Server CTE查询指定AreaID的所有上级父节点

现有表结构与测试数据 #

表结构定义 #

CREATE TABLE [dbo].[Areas](
    [AreaID] [bigint],
    [ParentArea] [bigint] NULL,
    CONSTRAINT [PK_Areas] PRIMARY KEY CLUSTERED ([AreaID] ASC)
);

测试数据插入 #

INSERT INTO Areas (AreaID,ParentArea) VALUES (142,null);
INSERT INTO Areas (AreaID,ParentArea) VALUES (143,142);
INSERT INTO Areas (AreaID,ParentArea) VALUES (144,143);
INSERT INTO Areas (AreaID,ParentArea) VALUES (148,144);

需求描述 #

查询指定AreaID（示例为148）的所有上级父节点，将结果以横向列的形式展示，列依次为目标AreaID、第1级父节点、第2级父节点……无对应层级则显示NULL，期望输出：

AreaID      1st       2nd        3rd       4th
--------------------------------------------------------
 148        144       143        142       NULL

原有尝试的问题 #

原有CTE的递归逻辑错误，未正确向上遍历所有父节点，仅能获取固定2级父节点，代码及结果如下：

原有查询代码 #

WITH  descendant 
   AS (SELECT  AreaID AS AreaID,
               ParentArea
         FROM  [dbo].[Areas]
        WHERE  AreaID = 148

 UNION ALL

       SELECT  t.AreaID,
               t.ParentArea
         FROM [dbo].[Areas] t
         JOIN descendant d
           ON t.ParentArea = d.AreaID
       )

       SELECT d.AreaID AS AreaID,
              d.ParentArea AS [1st],
              a.ParentArea AS [2nd]
         FROM  descendant d
         JOIN [dbo].[Areas] a
           ON d.ParentArea = a.AreaID

原有查询结果 #

AreaID       1st       2nd
---------------------------
148          144       143

正确的SQL Server CTE写法 #

静态层级查询（适用于已知最大层级的场景） #

WITH ParentHierarchy AS (
    -- 初始化：获取目标节点的直接父节点，标记为第1级
    SELECT 
        148 AS TargetAreaID,
        ParentArea AS ParentID,
        1 AS ParentLevel
    FROM Areas
    WHERE AreaID = 148
    UNION ALL
    -- 递归遍历上级父节点，层级递增
    SELECT 
        ph.TargetAreaID,
        a.ParentArea AS ParentID,
        ph.ParentLevel + 1 AS ParentLevel
    FROM Areas a
    JOIN ParentHierarchy ph ON a.AreaID = ph.ParentID
    WHERE a.ParentArea IS NOT NULL -- 父节点为空时停止递归
)
-- 合并目标节点自身信息，将层级数据转为横向列
SELECT 
    TargetAreaID AS AreaID,
    MAX(CASE WHEN ParentLevel = 1 THEN ParentID END) AS [1st],
    MAX(CASE WHEN ParentLevel = 2 THEN ParentID END) AS [2nd],
    MAX(CASE WHEN ParentLevel = 3 THEN ParentID END) AS [3rd],
    MAX(CASE WHEN ParentLevel = 4 THEN ParentID END) AS [4th]
FROM (
    -- 添加目标节点自身的行，确保结果包含目标ID
    SELECT TargetAreaID, NULL AS ParentID, 0 AS ParentLevel
    FROM (SELECT DISTINCT TargetAreaID FROM ParentHierarchy) t
    UNION ALL
    SELECT * FROM ParentHierarchy
) AS CombinedData
GROUP BY TargetAreaID;

动态层级查询（适用于不确定最大层级的场景） #

如果父节点层级不固定，可以用动态SQL自动生成对应列：

DECLARE @TargetID BIGINT = 148;
DECLARE @MaxLevel INT;
DECLARE @PivotColumns NVARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);

-- 先递归获取所有父节点层级
WITH ParentHierarchy AS (
    SELECT 
        @TargetID AS TargetAreaID,
        ParentArea AS ParentID,
        1 AS ParentLevel
    FROM Areas
    WHERE AreaID = @TargetID
    UNION ALL
    SELECT 
        ph.TargetAreaID,
        a.ParentArea AS ParentID,
        ph.ParentLevel + 1 AS ParentLevel
    FROM Areas a
    JOIN ParentHierarchy ph ON a.AreaID = ph.ParentID
    WHERE a.ParentArea IS NOT NULL
)
SELECT @MaxLevel = ISNULL(MAX(ParentLevel), 0) FROM ParentHierarchy;

-- 生成PIVOT需要的列名（[1st],[2nd],...）
SET @PivotColumns = STUFF((
    SELECT ',' + QUOTENAME(CONCAT(ParentLevel, 'th'))
    FROM (SELECT DISTINCT ParentLevel FROM ParentHierarchy) t
    ORDER BY ParentLevel
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

-- 构建动态SQL
SET @SQL = N'
WITH ParentHierarchy AS (
    SELECT 
        ' + CAST(@TargetID AS NVARCHAR) + ' AS TargetAreaID,
        ParentArea AS ParentID,
        1 AS ParentLevel
    FROM Areas
    WHERE AreaID = ' + CAST(@TargetID AS NVARCHAR) + '
    UNION ALL
    SELECT 
        ph.TargetAreaID,
        a.ParentArea AS ParentID,
        ph.ParentLevel + 1 AS ParentLevel
    FROM Areas a
    JOIN ParentHierarchy ph ON a.AreaID = ph.ParentID
    WHERE a.ParentArea IS NOT NULL
)
SELECT 
    TargetAreaID AS AreaID,
    ' + @PivotColumns + '
FROM (
    SELECT 
        TargetAreaID,
        CONCAT(ParentLevel, ''th'') AS LevelName,
        ParentID
    FROM ParentHierarchy
) AS SourceData
PIVOT (
    MAX(ParentID)
    FOR LevelName IN (' + @PivotColumns + ')
) AS PivotResult;';

-- 执行动态SQL
EXEC sp_executesql @SQL;

代码说明 #

1. 静态查询：通过递归CTE遍历所有父节点并标记层级，再用条件聚合将纵向的层级数据转为横向列，适合层级数量固定的场景。
2. 动态查询：先通过递归获取最大层级，自动生成对应列名，再用动态PIVOT实现横向展示，适合层级数量不确定的场景。

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