如何编写递归SQL查询从subordination表中获取指定上级X的所有下属

PostgreSQL 内置WITH RECURSIVE递归公共表表达式语法，可以直接实现上下级层级遍历查询，具体实现如下：

完整查询示例 #

WITH RECURSIVE subordinate_tree AS (
    -- 锚点查询：获取目标上级的直接下属
    SELECT subordinate_id
    FROM subordination
    WHERE superior_id = 1080 -- 此处替换为你要查询的指定上级ID X
    UNION ALL
    -- 递归查询：循环遍历获取每一层下属的下级
    SELECT s.subordinate_id
    FROM subordination s
    INNER JOIN subordinate_tree st ON s.superior_id = st.subordinate_id
)
-- 输出结果，DISTINCT可选，无脏数据循环依赖时可省略
SELECT DISTINCT subordinate_id FROM subordinate_tree;

效果验证 #

以你提供的样例数据测试，查询上级ID为1080时，返回结果为：1070、1060、1020、1030、1040，完全覆盖所有层级的下属。

可选扩展项 #

• 若需要展示下属层级：可在锚点部分新增1 AS depth字段，递归部分改为st.depth + 1 AS depth，即可得到每个下属距离指定上级的层级数
• 若存在循环依赖脏数据：可在递归查询的WHERE条件中增加st.depth < 20（数值可按需调整），避免无限递归
• 若需要关联返回员工姓名等其他字段：在SELECT子句中补充对应字段即可，需保证锚点和递归部分的字段数量、类型完全一致

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