SQL Server递归生成完整嵌套成员关系的问题求助

一、更优的循环规避方法（替代字符串模糊匹配） #

1. 使用SQL Server HierarchyID类型 #

HierarchyID是SQL Server专为层级数据设计的内置类型，能高效存储节点路径，判断节点是否已存在于路径中的性能远高于字符串模糊匹配，同时天然支持层级关系的解析。

修改后的查询示例：

create table groups_groups (
    group_id int not null,
    child_id int not null
)

create table groups(group_id int not null)

insert into  groups_groups values (78,80)
insert into  groups_groups values (80,79)
insert into groups values (78)
insert into groups values (70)
insert into groups values (80)
;

with cte as (
    select
        0 as gglevel,
        0 as parentid,
        g.group_id,
        -- 初始化路径：根节点 -> 当前组ID
        hierarchyid::GetRoot().GetDescendant(null, hierarchyid::Parse('/' + CAST(g.group_id AS VARCHAR) + '/')) as path
    from groups g
    union all
    select
        c.gglevel + 1 as gglevel,
        gg.group_id as parentid,
        gg.child_id,
        -- 扩展路径：在当前路径后追加子节点
        c.path.GetDescendant(null, hierarchyid::Parse('/' + CAST(gg.child_id AS VARCHAR) + '/')) as path
    from cte c
    join groups_groups gg
        on gg.group_id = c.group_id
    -- 判断子节点是否已在路径中，避免循环
    where c.path.IsDescendantOf(hierarchyid::Parse('/' + CAST(gg.child_id AS VARCHAR) + '/')) = 0
)
select
    gglevel,
    parentid,
    group_id,
    -- 将HierarchyID转换为可读的路径字符串
    path.ToString() as full_path
from cte
order by group_id, gglevel

2. 使用节点ID集合（SQL Server 2016+兼容） #

如果无法使用HierarchyID，可以用字符串存储已访问的节点ID，通过CHARINDEX替代模糊匹配判断节点是否重复，性能优于原方法：

with cte as (
    select
        0 as gglevel,
        0 as parentid,
        g.group_id,
        CAST(',' + CAST(g.group_id AS VARCHAR) + ',' AS VARCHAR(MAX)) as visited_nodes
    from groups g
    union all
    select
        c.gglevel + 1 as gglevel,
        gg.group_id as parentid,
        gg.child_id,
        CAST(c.visited_nodes + CAST(gg.child_id AS VARCHAR) + ',' AS VARCHAR(MAX)) as visited_nodes
    from cte c
    join groups_groups gg
        on gg.group_id = c.group_id
    -- 检查子节点是否未被访问过
    where CHARINDEX(',' + CAST(gg.child_id AS VARCHAR) + ',', c.visited_nodes) = 0
)
select * from cte
order by group_id, gglevel

二、关于“重复行”的说明 #

你当前结果中group_id=79的两行是不同路径的合法结果：

• 路径1：0→80→79（gglevel=1）
• 路径2：0→78→80→79（gglevel=2）

这正是你需要的“获取79的所有可能组合”，并非冗余重复。如果需要更直观展示完整路径，可以参考上述HierarchyID示例中的full_path字段，或通过STRING_AGG聚合路径节点。

三、额外优化建议 #

• 为groups_groups表的group_id和child_id建立联合索引，提升递归JOIN的性能。
• 若只需最终完整路径而非每层级记录，可在最终查询中用STRING_AGG按层级聚合节点ID，精简结果。

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