SQL Server中含循环父子关系的CTE如何展平列表？

这里的父子关系类似谷歌邮件组：父组可包含多个子组，子组也能包含祖先链中的父组，目标是找出某组关联的所有节点，递归循环本身并非问题。

给定基础数据与原递归CTE查询如下：

CREATE TABLE SQLTest (
    Parent NVARCHAR(100) NULL
    , Child  NVARCHAR(100) NULL
)

INSERT INTO SQLTest
VALUES 
('A','B'),
('B','C'),
('C','A'),
('A','D'),
('X','Y')

原查询：

with x (Parent,Child) as (
    select Parent, Child
    from SQLTest
    where Parent = 'A'
    union all
    select T.Parent,T.Child
    from SQLTest T
    join x on x.Child = T.Parent
)
select *
from x;

该查询在PostgreSQL中可正常运行（不会重复处理已发现记录），但在SQL Server 2022中会触发无限循环。已知可通过物化路径检测循环，但因使用byte[]类型的ULID，转换与匹配操作繁琐，需更简便高效的解决方案，预期输出从节点A可达的所有节点：

A
B
C
D

核心思路是在递归CTE中跟踪已访问的节点集合，每次递归时跳过已访问的节点，从根源避免无限循环。

方法1：字符串存储已访问节点（适合短标识场景） #

实现简单，无需额外类型定义，适合字符串类型的节点标识：

WITH RecursiveCTE AS (
    -- 锚点：初始节点A的直接关联记录，同时记录已访问节点
    SELECT 
        Parent,
        Child,
        CAST(',' + Parent + ',' + Child + ',' AS NVARCHAR(MAX)) AS VisitedNodes
    FROM SQLTest
    WHERE Parent = 'A'
    UNION ALL
    -- 递归：仅处理未访问过的节点
    SELECT 
        T.Parent,
        T.Child,
        CAST(r.VisitedNodes + T.Child + ',' AS NVARCHAR(MAX)) AS VisitedNodes
    FROM SQLTest T
    JOIN RecursiveCTE r ON r.Child = T.Parent
    WHERE CHARINDEX(',' + T.Child + ',', r.VisitedNodes) = 0
)
-- 提取所有唯一可达节点
SELECT DISTINCT Node
FROM (
    SELECT Parent AS Node FROM RecursiveCTE
    UNION ALL
    SELECT Child AS Node FROM RecursiveCTE
    UNION ALL
    SELECT 'A' AS Node -- 补充初始节点
) AS AllNodes
WHERE Node IS NOT NULL
ORDER BY Node;

方法2：表类型存储已访问节点（针对ULID优化） #

针对byte[]类型的ULID，通过自定义表类型存储已访问节点，避免频繁格式转换，性能更优：

1. 先创建自定义表类型：

CREATE TYPE dbo.UlidList AS TABLE (UlidValue VARBINARY(16) PRIMARY KEY);

2. 编写递归CTE：

WITH RecursiveCTE AS (
    SELECT 
        Parent,
        Child,
        -- 初始化已访问ULID集合
        CAST(
            (SELECT CAST(Parent AS VARBINARY(16)) UNION ALL SELECT CAST(Child AS VARBINARY(16))) 
            AS dbo.UlidList
        ) AS VisitedUlids
    FROM SQLTest
    WHERE Parent = 'A' -- 若Parent为ULID，替换为对应byte[]值
    UNION ALL
    SELECT 
        T.Parent,
        T.Child,
        -- 更新已访问ULID集合
        (SELECT * FROM r.VisitedUlids UNION ALL SELECT CAST(T.Child AS VARBINARY(16))) AS VisitedUlids
    FROM SQLTest T
    JOIN RecursiveCTE r ON r.Child = T.Parent
    -- 检查当前节点是否已访问
    WHERE NOT EXISTS (
        SELECT 1 
        FROM r.VisitedUlids 
        WHERE UlidValue = CAST(T.Child AS VARBINARY(16))
    )
)
-- 提取并转换回ULID字符串格式
SELECT DISTINCT 
    CONVERT(NVARCHAR(26), NodeUlid) AS Node
FROM (
    SELECT CAST(Parent AS VARBINARY(16)) AS NodeUlid FROM RecursiveCTE
    UNION ALL
    SELECT CAST(Child AS VARBINARY(16)) AS NodeUlid FROM RecursiveCTE
    UNION ALL
    SELECT CAST('A' AS VARBINARY(16)) AS NodeUlid -- 补充初始节点
) AS AllNodes
ORDER BY Node;

关键说明 #

• 两种方法均通过跟踪已访问节点避免重复进入循环路径；
• 方法1适合快速实现，适用于字符串类型的节点标识；
• 方法2针对byte[]类型ULID优化，避免了繁琐的格式转换开销。

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