如何在SQL Server中使用两张表编写递归CTE查询？

场景说明 #

现有两张表：

• Table1：存储初始节点ID

  ID
  4
  3079
  

• Table2：存储节点间的关联关系（Source为父节点，Target为子节点）

  Source, Target
  5,     4
  6,     5
  3080,  3079
  3081,  3080
  

需要实现：以Table1的ID对应Table2的Target为起点，递归向上溯源所有关联的Source节点，最终输出每个初始ID对应的所有溯源节点（包含自身）。

递归CTE查询语句 #

WITH RecursiveCTE AS (
    -- 锚点成员：初始化，取Table1的ID作为初始节点
    SELECT 
        t1.ID AS OriginalID,
        t1.ID AS ResultColumn
    FROM Table1 t1

    UNION ALL

    -- 递归成员：向上关联，将当前节点作为Target，获取对应的Source节点
    SELECT 
        r.OriginalID,
        t2.Source AS ResultColumn
    FROM RecursiveCTE r
    JOIN Table2 t2 ON r.ResultColumn = t2.Target
)
-- 输出最终结果，按原始ID和节点值排序
SELECT 
    OriginalID AS ID,
    ResultColumn AS [Result column]
FROM RecursiveCTE
ORDER BY OriginalID, ResultColumn;

查询结果 #

执行上述语句后，将得到期望的输出：

ID  Result column
4   4
4   5
4   6
3079    3079
3079    3080
3079    3081

逻辑说明 #

1. 锚点成员：直接从Table1获取初始ID，同时将该ID作为第一个ResultColumn，保证每个初始ID都包含自身节点。
2. 递归成员：通过递归CTE的ResultColumn关联Table2的Target，获取对应的Source节点，继续向上溯源，直到没有关联的节点为止。
3. 最终查询：将递归结果中的OriginalID作为ID，ResultColumn作为结果列，排序后输出完整的溯源路径。

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