如何在Azure SQL中通过递归查询获取指定角色的所有子角色？

嘿，我之前刚好处理过类似的树形层级查询需求！在Azure SQL里，用**递归CTE（公用表表达式）**就能完美实现你要的功能，和Neo4j的递归匹配逻辑思路一致，但写法是标准的SQL风格，我给你一步步拆解：

先假设你的表结构（可对应自己的表修改） #

我先基于你提到的roles节点表和canmanage边表，定义最常见的字段结构，你可以直接替换成自己的实际字段：

• roles表：role_id（角色ID，主键）、role_name（角色名称）
• canmanage表：parent_role_id（父角色ID）、child_role_id（子角色ID，关联roles.role_id）

递归CTE实现查询 #

递归CTE分为两部分：锚点成员（指定查询的起始角色）和递归成员（循环遍历所有子层级）。

示例1：查询指定角色ID的所有子角色（不含自身） #

-- 定义递归CTE
WITH RecursiveChildRoles AS (
    -- 锚点成员：获取目标角色的直接子角色
    SELECT 
        cr.child_role_id,
        r.role_name,
        1 AS hierarchy_level  -- 可选字段，标记当前角色在层级中的深度
    FROM canmanage cr
    JOIN roles r ON cr.child_role_id = r.role_id
    WHERE cr.parent_role_id = @TargetRoleId  -- 替换成你要查询的角色ID，或用参数传入

    UNION ALL

    -- 递归成员：继续查找子角色的子角色，直到没有下一层为止
    SELECT 
        cr.child_role_id,
        r.role_name,
        rcr.hierarchy_level + 1 AS hierarchy_level
    FROM canmanage cr
    -- 关联上一轮递归查到的角色，作为父角色继续查找
    JOIN RecursiveChildRoles rcr ON cr.parent_role_id = rcr.child_role_id
    JOIN roles r ON cr.child_role_id = r.role_id
)
-- 最终输出所有子角色
SELECT child_role_id, role_name, hierarchy_level
FROM RecursiveChildRoles
ORDER BY hierarchy_level, role_name;

示例2：用角色名称作为输入查询 #

如果你习惯用角色名称而非ID查询，可以先通过名称找到对应的ID，或者直接修改锚点的条件：

WITH RecursiveChildRoles AS (
    SELECT 
        cr.child_role_id,
        r.role_name,
        1 AS hierarchy_level
    FROM canmanage cr
    JOIN roles r ON cr.child_role_id = r.role_id
    -- 通过角色名称定位父角色
    WHERE EXISTS (
        SELECT 1 FROM roles parent_r 
        WHERE parent_r.role_id = cr.parent_role_id 
        AND parent_r.role_name = '目标角色名称'  -- 替换成你的目标角色名
    )

    UNION ALL

    SELECT 
        cr.child_role_id,
        r.role_name,
        rcr.hierarchy_level + 1 AS hierarchy_level
    FROM canmanage cr
    JOIN RecursiveChildRoles rcr ON cr.parent_role_id = rcr.child_role_id
    JOIN roles r ON cr.child_role_id = r.role_id
)
SELECT child_role_id, role_name, hierarchy_level
FROM RecursiveChildRoles
ORDER BY hierarchy_level, role_name;

示例3：包含目标角色自身的查询 #

如果需要把输入的角色也包含在结果里，只需调整锚点成员，先选中目标角色本身，再递归子角色：

WITH RecursiveChildRoles AS (
    -- 锚点成员：先包含目标角色自己
    SELECT 
        r.role_id,
        r.role_name,
        0 AS hierarchy_level
    FROM roles r
    WHERE r.role_id = @TargetRoleId

    UNION ALL

    -- 递归成员：继续查找子角色
    SELECT 
        cr.child_role_id,
        r.role_name,
        rcr.hierarchy_level + 1 AS hierarchy_level
    FROM canmanage cr
    JOIN RecursiveChildRoles rcr ON cr.parent_role_id = rcr.role_id
    JOIN roles r ON cr.child_role_id = r.role_id
)
SELECT role_id, role_name, hierarchy_level
FROM RecursiveChildRoles
ORDER BY hierarchy_level, role_name;

注意事项 #

• 避免循环引用：如果你的canmanage表存在循环（比如A管理B，B又管理A），递归会无限循环。Azure SQL默认递归层数限制是100，若你的层级超过100，可以在查询末尾加OPTION (MAXRECURSION 0)取消限制，但一定要确保没有循环引用。
• 性能优化：确保canmanage表的parent_role_id和child_role_id字段有索引，这样递归查询的效率会大幅提升。

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