如何查询UserType表中指定节点的所有层级下级数据？

表结构 #

首先明确UserType表的结构：

需求说明 #

给定一个UserType值，查询该类型的所有下级层级（从直接子级开始，遍历到最底层节点）。例如：

• 指定SuperAdmin时，返回Admin、Manager、Sale、Customer
• 指定Manager时，返回Sale、Customer

递归CTE实现 #

使用**递归公共表表达式（CTE）**可以实现这种层级遍历，以下是通用SQL语句（适用于MySQL 8.0+、SQL Server、PostgreSQL等支持递归CTE的数据库）：

WITH RECURSIVE UserTypeHierarchy AS (
    -- 锚点成员：获取指定UserType的直接子级
    SELECT ChildUserType AS UserType
    FROM UserType
    WHERE UserType = 'SuperAdmin' -- 替换为目标UserType
    UNION ALL
    -- 递归成员：遍历下级的子级，直到无后续子级
    SELECT ut.ChildUserType AS UserType
    FROM UserType ut
    JOIN UserTypeHierarchy uth ON ut.UserType = uth.UserType
    WHERE ut.ChildUserType IS NOT NULL
)
SELECT UserType
FROM UserTypeHierarchy;

示例使用 #

1. 查询SuperAdmin的所有下级：将语句中的'SuperAdmin'作为目标值，执行后返回：

2. 查询Manager的所有下级：替换目标值为'Manager'，执行后返回：

逻辑说明 #

• 锚点成员：先定位目标UserType对应的直接子级
• 递归成员：以上一轮结果中的UserType为父级，继续查找其子级，循环至无后续子级（ChildUserType为null）
• UNION ALL合并锚点与递归结果，保证层级完整性

内容的提问来源于stack exchange，提问作者Huy Hoang Nguyen