WITH RecursiveRoles AS (
-- 基础情况：选择所有顶级角色（没有父角色的角色）
SELECT RoleId, RoleName, RoleId AS OriginalRoleId, 0 AS Level
FROM webpages_Roles
WHERE RoleId NOT IN (SELECT InheritedRoleId FROM role_inherited)
UNION ALL
-- 递归部分：选择所有子角色
SELECT ri.InheritedRoleId, wr.RoleName, rr.OriginalRoleId, rr.Level + 1
FROM role_inherited ri
INNER JOIN webpages_Roles wr ON ri.InheritedRoleId = wr.RoleId
INNER JOIN RecursiveRoles rr ON ri.RoleId = rr.RoleId
)
SELECT L.Level, L.OriginalRoleId, R.RoleName, L.RoleId, L.RoleName
FROM RecursiveRoles L left outer join webpages_Roles R ON L.OriginalRoleId = R.RoleId
--WHERE L.OriginalRoleId <> L.RoleId
ORDER BY L.Level, R.RoleName, L.RoleName;
陕公网安备：61019002000258 |
隐私政策 |
网站地图
