SQL Server使用递归CTE实现AD嵌套组隶属关系查询

核心实现思路 #

用递归CTE完成层级遍历，逻辑拆分为两部分：

• 锚定节点：定位传入的目标起始组，作为隶属链的起点
• 递归遍历：循环关联组映射表，逐层向上查找当前组的上级组，直到上级组为NULL（到达顶级组）时终止遍历
• 遍历过程中同步拼接隶属路径，最终取最深层级的路径即为完整隶属关系链

可直接运行的实现代码 #

首先假设你的查找表名为ADGroupLookup，包含GroupName、Memberof两个字段：

-- 声明要查询的目标组参数，可按需替换
DECLARE @TargetGroupName VARCHAR(100) = 'Grp3';

WITH GroupRecursion AS (
    -- 锚定部分：初始化起始组
    SELECT
        GroupName,
        Memberof,
        CAST(GroupName AS VARCHAR(MAX)) AS FullPath,
        1 AS Level
    FROM ADGroupLookup
    WHERE GroupName = @TargetGroupName

    UNION ALL

    -- 递归部分：逐层向上匹配上级组
    SELECT
        g.GroupName,
        g.Memberof,
        CAST(CONCAT(r.FullPath, '->', g.GroupName) AS VARCHAR(MAX)) AS FullPath,
        r.Level + 1 AS Level
    FROM ADGroupLookup g
    INNER JOIN GroupRecursion r
        ON g.GroupName = r.Memberof
)
-- 取层级最深的记录，即为完整隶属链
SELECT TOP 1 FullPath AS NestedGroupMembership
FROM GroupRecursion
ORDER BY Level DESC
-- 放开递归层级限制，适配深层嵌套场景
OPTION (MAXRECURSION 0);

结果验证 #

基于你提供的测试数据执行：

• 当@TargetGroupName设为Grp3时，返回结果为Grp3->Grp2->Grp4，符合预期
• 当@TargetGroupName设为Grp2时，返回结果为Grp2->Grp4，符合预期

适配优化点 #

• 若存在组名大小写混用的情况（如GRP3、Grp3同时存在），可在关联条件中增加大小写不敏感排序规则，避免匹配遗漏：

  ON g.GroupName COLLATE SQL_Latin1_General_CP1_CI_AS = r.Memberof COLLATE SQL_Latin1_General_CP1_CI_AS
  

• 若存在异常循环嵌套（如A隶属于B、B又隶属于A），可在递归部分增加路径重复检测，避免死循环：

  WHERE CHARINDEX(g.GroupName, r.FullPath) = 0
  

• 你当前数据量仅5000个组、嵌套组占比极低，该写法的执行效率完全满足报表需求，无需额外性能优化。

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