递归SQL实现用户权限与角色父子层级查询问题

现有三张数据库表：

• Permission表：包含Id、Name、Permission列；Permission列为NULL时表示角色，非NULL时表示具体权限。
• Permission_Permission表：包含IdParentPermission（父节点ID）和IdChildPermission（子节点ID）列，维护权限/角色的父子层级关系。
• Users_Permissions表：包含IdUser和IdPermission列，关联用户与权限/角色。

示例数据 #

Permission表

Permission_Permission表

Users_Permissions表

需求：编写递归SQL脚本，查询指定用户拥有的所有权限/角色及其所有子节点，输出格式为IdUser、Id、Name、Permission、IdParentPermission。

原代码存在以下核心问题：

1. 递归锚点错误：直接获取所有Permission_Permission数据，未限定到指定用户的权限范围，导致递归范围偏离目标。
2. 递归方向错误：尝试从子节点向上追溯父节点，而非从用户拥有的节点向下递归子节点。
3. 用户关联逻辑错误：在最终查询阶段才关联用户，仅能筛选出用户直接拥有的子节点，遗漏了深层递归的子节点。

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;

DECLARE @IdUser INT = 10;

WITH PermissionHierarchy AS (
    -- 锚点：获取用户直接拥有的所有权限/角色，包含父节点ID（若存在）
    SELECT 
        up.IdUser,
        p.Id,
        p.Name,
        p.Permission,
        pp.IdParentPermission
    FROM Users_Permissions up
    INNER JOIN Permission p ON up.IdPermission = p.Id
    LEFT JOIN Permission_Permission pp ON p.Id = pp.IdChildPermission
    WHERE up.IdUser = @IdUser

    UNION ALL

    -- 递归：逐层获取当前节点的所有子节点
    SELECT 
        ph.IdUser,
        child_p.Id,
        child_p.Name,
        child_p.Permission,
        pp.IdParentPermission
    FROM PermissionHierarchy ph
    INNER JOIN Permission_Permission pp ON ph.Id = pp.IdParentPermission
    INNER JOIN Permission child_p ON pp.IdChildPermission = child_p.Id
)
-- 去重并排序，避免同一权限通过不同路径重复出现
SELECT DISTINCT
    IdUser,
    Id,
    Name,
    Permission,
    IdParentPermission
FROM PermissionHierarchy
ORDER BY IdUser, Id;

1. 锚点部分：先获取指定用户直接关联的权限/角色，关联Permission表拿到基础信息，通过左连接Permission_Permission获取该节点的父ID（无父节点时为NULL）。
2. 递归部分：以锚点中的节点为父节点，递归查询所有子节点，确保只遍历该用户权限树内的节点。
3. 最终查询：用DISTINCT去重（同一权限可能通过多个角色路径被用户拥有），并按用户ID和节点ID排序，得到完整的权限/角色层级。

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