SQL Server多表递归查询：用户组有效权限获取问题

实现思路 #

通过递归CTE先遍历指定sourceid下所有组的父组链（包括组本身），再关联用户表得到所有拥有权限的用户，同时记录权限层级：

• 层级0：用户直接属于目标组（直接权限）
• 层级≥1：用户通过父组继承获得目标组权限（嵌套权限）

完整SQL代码 #

DECLARE @TargetSourceId INT = 5; -- 替换为你需要查询的sourceid

WITH GroupHierarchy AS (
    -- 锚点：指定sourceid下的所有组，层级0（组本身）
    SELECT 
        gid,
        name,
        gid AS target_gid,
        name AS target_name,
        0 AS hierarchy_level
    FROM [group]
    WHERE sourceid = @TargetSourceId

    UNION ALL

    -- 递归：向上遍历父组（childid为当前组，parentid为父组）
    SELECT 
        gg.parentid AS gid,
        g.name,
        gh.target_gid,
        gh.target_name,
        gh.hierarchy_level + 1 AS hierarchy_level
    FROM GroupHierarchy gh
    JOIN groupgroup gg ON gh.gid = gg.childid
    JOIN [group] g ON gg.parentid = g.gid
)
-- 关联用户表，去重并保留每个用户-组的最小权限层级
SELECT
    uid,
    gid,
    group_name,
    permission_level
FROM (
    SELECT
        ug.uid,
        gh.target_gid AS gid,
        gh.target_name AS group_name,
        gh.hierarchy_level AS permission_level,
        -- 取每个用户-组的最小层级（最直接的权限路径）
        ROW_NUMBER() OVER (PARTITION BY ug.uid, gh.target_gid ORDER BY gh.hierarchy_level) AS rn
    FROM GroupHierarchy gh
    JOIN usergroup ug ON gh.gid = ug.gid
) t
WHERE rn = 1
ORDER BY uid, permission_level, gid;

代码说明 #

1. 递归CTE GroupHierarchy

   • 锚点部分：筛选出指定sourceid下的所有组，作为权限链的终点（目标组），层级设为0。
   • 递归部分：通过groupgroup表向上查找父组，每次层级加1，同时保留目标组的信息，直到没有父组为止。

2. 关联用户表并去重

   • 将层级表与usergroup关联，找到所有属于父组链的用户。
   • 用ROW_NUMBER()过滤掉重复的用户-组关系，只保留最直接的权限路径（最小层级）。如果需要保留所有权限路径，可去掉子查询，直接用DISTINCT去重。

示例结果（sourceid=5） #

关键注意事项 #

• 表名group是SQL Server关键字，必须用方括号[group]包裹。
• 如果不需要保留最小层级，可简化为直接关联后用DISTINCT去重，适合需要展示所有权限路径的场景。

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