T-SQL递归查询：获取含祖先节点关联表T2的全量数据

这个需求我之前处理过类似的，用T-SQL的递归CTE就能完美解决，给你一步步拆解清楚：

核心思路是先获取每个ClassID的所有祖先节点（包括自身），再关联T2表拿到对应的所有数据。

1. 先准备测试数据（方便你对照验证） #

先模拟示例中的T1层级表和T2关联表，你可以直接运行这段代码创建测试环境：

-- 创建T1层级表
CREATE TABLE T1 (
    ClassID INT PRIMARY KEY,
    ParentClassID INT NULL FOREIGN KEY REFERENCES T1(ClassID)
);

INSERT INTO T1 (ClassID, ParentClassID)
VALUES 
(1, NULL),  -- 根节点
(2, 1),
(3, 2),
(4, 1),
(5, 4);

-- 创建T2关联数据表
CREATE TABLE T2 (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    ClassID INT NOT NULL FOREIGN KEY REFERENCES T1(ClassID),
    DataValue VARCHAR(50) NOT NULL
);

INSERT INTO T2 (ClassID, DataValue)
VALUES
(1, 'A'),
(1, 'B'),
(2, 'C'),
(3, 'D'),
(4, 'E'),
(5, 'F');

2. 用递归CTE获取所有祖先节点（含自身） #

递归CTE分两部分：

• 锚点成员：先把每个ClassID自身加入结果集
• 递归成员：不断向上查找父节点，直到找到根节点（ParentClassID为NULL）

WITH ClassHierarchy AS (
    -- 锚点：每个节点自身
    SELECT 
        ClassID AS OriginalClassID,  -- 记录原始的ClassID
        ClassID AS HierarchyClassID, -- 当前遍历到的层级节点ID
        ParentClassID
    FROM T1

    UNION ALL

    -- 递归：向上查找父节点
    SELECT 
        ch.OriginalClassID,
        t.ClassID AS HierarchyClassID,
        t.ParentClassID
    FROM ClassHierarchy ch
    JOIN T1 t ON ch.ParentClassID = t.ClassID
)

3. 关联T2表获取最终结果 #

把上面的CTE和T2表关联，就能得到每个原始ClassID对应的所有自身+祖先的T2数据：

WITH ClassHierarchy AS (
    SELECT 
        ClassID AS OriginalClassID,
        ClassID AS HierarchyClassID,
        ParentClassID
    FROM T1

    UNION ALL

    SELECT 
        ch.OriginalClassID,
        t.ClassID AS HierarchyClassID,
        t.ParentClassID
    FROM ClassHierarchy ch
    JOIN T1 t ON ch.ParentClassID = t.ClassID
)
SELECT 
    ch.OriginalClassID,       -- 原始的ClassID
    t2.ClassID AS SourceClassID, -- 提供数据的节点ID（自身或祖先）
    t2.DataValue              -- 对应的T2数据
FROM ClassHierarchy ch
JOIN T2 t2 ON ch.HierarchyClassID = t2.ClassID
ORDER BY ch.OriginalClassID, t2.ClassID;

结果示例 #

比如对于OriginalClassID=3，它的祖先节点是2、1，所以结果会包含：

• 3自身的T2数据：D
• 父节点2的T2数据：C
• 祖父节点1的T2数据：A、B

特殊情况处理 #

如果你的T1表存在循环引用（比如ClassID=2的ParentClassID指向3，形成闭环），可以在递归CTE里加入路径校验避免死循环：

WITH ClassHierarchy AS (
    SELECT 
        ClassID AS OriginalClassID,
        ClassID AS HierarchyClassID,
        ParentClassID,
        -- 用字符串记录遍历路径，防止循环
        CAST(',' + CAST(ClassID AS VARCHAR(10)) + ',' AS VARCHAR(MAX)) AS NodePath
    FROM T1

    UNION ALL

    SELECT 
        ch.OriginalClassID,
        t.ClassID AS HierarchyClassID,
        t.ParentClassID,
        ch.NodePath + CAST(t.ClassID AS VARCHAR(10)) + ','
    FROM ClassHierarchy ch
    JOIN T1 t ON ch.ParentClassID = t.ClassID
    -- 排除已经在路径里的节点，避免循环
    WHERE ch.NodePath NOT LIKE '%,' + CAST(t.ClassID AS VARCHAR(10)) + ',%'
)
-- 后续关联T2的逻辑不变
SELECT 
    ch.OriginalClassID,
    t2.ClassID AS SourceClassID,
    t2.DataValue
FROM ClassHierarchy ch
JOIN T2 t2 ON ch.HierarchyClassID = t2.ClassID
ORDER BY ch.OriginalClassID, t2.ClassID;

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