PostgreSQL递归自连接实现指定用户多级管理者查询需求

我有一张名为profiles的表，存储用户及其管理者的层级数据，需要编写SQL查询获取指定用户的所有层级管理者。其中只有text字段值为'A'的记录，其manager_id才是正式的上级管理者。

表结构及数据如下：

预期结果：

• 指定user_id=50时，输出所有管理者：20,21,22
• 指定user_id=20时，输出所有管理者：21,22

针对这种层级嵌套的关系，递归公共表表达式（CTE）是最直接的解决方式，以下是完整SQL：

WITH RECURSIVE manager_hierarchy AS (
    -- 第一步：获取目标用户的直接正式管理者
    SELECT 
        manager_id
    FROM profiles
    WHERE user_id = ?  -- 替换为你要查询的user_id，比如50或20
      AND text = 'A'
      AND manager_id IS NOT NULL
    
    UNION ALL
    
    -- 第二步：递归查找上级的上级，直到没有更高级管理者
    SELECT 
        p.manager_id
    FROM profiles p
    JOIN manager_hierarchy mh ON p.user_id = mh.manager_id
    WHERE p.text = 'A'
      AND p.manager_id IS NOT NULL
)
-- 将结果拼接成逗号分隔的字符串（符合示例格式）
SELECT GROUP_CONCAT(manager_id ORDER BY manager_id) AS all_managers
FROM manager_hierarchy;

细节说明 #

1. 递归逻辑：
   • 初始查询先定位目标用户的直接有效上级（仅text='A'且manager_id非空的记录）
   • 递归部分把已找到的管理者当作新的用户，循环查询他们的正式上级，直到找不到更高级的管理者为止
2. 结果格式调整：
   • 如果不需要逗号拼接的结果，直接执行SELECT manager_id FROM manager_hierarchy ORDER BY manager_id;，就能得到每条管理者ID单独一行的输出
3. 参数使用：把SQL中的?替换为具体的user_id值即可，比如查询user_id=50时，替换为50

验证测试 #

• 当user_id=50时，执行后得到结果：20,21,22
• 当user_id=20时，执行后得到结果：21,22

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