求助：PostgreSQL递归查询实现主题的祖先与后代查询

我完全懂你啃PostgreSQL递归CTE文档时的头大感——这种嵌套查询一开始确实不好绕明白，不过结合你的表结构和需求，咱们可以直接上手写可运行的查询，边看边理解。

先回顾下你的数据结构：subjects存节点基础信息，subject_associations存父子关联（一个节点可以有多个父/子节点），顶层节点无父节点，底层节点无子节点。

一、根据parent_id获取所有后代节点 #

递归查询的核心是锚点成员（起始节点）+递归成员（迭代查找逻辑），咱们用WITH RECURSIVE来实现：

通用查询模板 #

WITH RECURSIVE descendant_cte AS (
    -- 锚点：获取目标parent_id对应的直接子节点
    SELECT child_id
    FROM public.subject_associations
    WHERE parent_id = {目标parent_id}  -- 替换成你要查询的parent_id，比如1
    
    UNION ALL
    
    -- 递归：把当前找到的子节点当作新的parent_id，继续查找它们的子节点
    SELECT sa.child_id
    FROM public.subject_associations sa
    JOIN descendant_cte cte ON sa.parent_id = cte.child_id
)
-- 最终返回所有后代节点的id（需要名称可关联subjects表）
SELECT DISTINCT child_id FROM descendant_cte ORDER BY child_id;

示例：查询parent_id=1的后代 #

把{目标parent_id}替换为1，运行后会返回：3,4,5,6,7,8，完全符合你的预期。

逻辑拆解 #

1. 锚点部分先拿到parent_id=1的直接子节点：3和4；
2. 递归部分把3、4当作新的parent_id，找它们的子节点——3没有子节点，4的子节点是8和5；
3. 继续迭代：8无后续子节点，5的子节点是6；
4. 再迭代：6的子节点是7，7无后续节点，递归自动终止；
5. 用DISTINCT去重（避免重复关联的情况），排序后输出所有后代。

二、根据child_id获取所有祖先节点 #

思路和后代查询完全反过来：锚点拿直接父节点，递归迭代查找父节点的父节点。

通用查询模板 #

WITH RECURSIVE ancestor_cte AS (
    -- 锚点：获取目标child_id对应的直接父节点
    SELECT parent_id
    FROM public.subject_associations
    WHERE child_id = {目标child_id}  -- 替换成你要查询的child_id，比如7
    
    UNION ALL
    
    -- 递归：把当前找到的父节点当作新的child_id，继续查找它们的父节点
    SELECT sa.parent_id
    FROM public.subject_associations sa
    JOIN ancestor_cte cte ON sa.child_id = cte.parent_id
)
-- 最终返回所有祖先节点的id（需要名称可关联subjects表）
SELECT DISTINCT parent_id FROM ancestor_cte ORDER BY parent_id;

示例：查询child_id=7的祖先 #

把{目标child_id}替换为7，运行后会返回：1,4,5,6（若需要和你预期的6,5,4,1顺序一致，把ORDER BY parent_id改成ORDER BY parent_id DESC即可）。

逻辑拆解 #

1. 锚点先拿到child_id=7的直接父节点：6；
2. 递归部分把6当作child_id，找到它的父节点：5；
3. 继续迭代：5的父节点是4，4的父节点是1；
4. 1无父节点，递归自动终止；
5. 去重排序后输出所有祖先。

额外小提示 #

• 如果需要同时获取节点名称，可以把查询和subjects表关联，比如：

  SELECT DISTINCT cte.child_id, s.name
  FROM descendant_cte cte
  JOIN public.subjects s ON cte.child_id = s.id
  ORDER BY cte.child_id;
  

• 因为你的数据没有循环（顶层无父、底层无子），所以不用额外处理递归终止的异常，PostgreSQL会自动停止迭代。

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