如何在PostgreSQL中用递归CTE查询获取子帖到根帖的所有帖子

给定如下Post表结构：
Post表

id: text 主键 非空
content: text 非空
author_id: text 外键关联User(id) 非空
parent_id: text 外键关联Post(id)

要通过子帖子ID查询从该子帖到根帖（parent_id为null的帖子）的所有帖子，可以用PostgreSQL的递归CTE实现，具体语句如下：

WITH RECURSIVE post_path AS (
    -- 锚点：定位目标子帖子
    SELECT id, content, author_id, parent_id, 1 AS depth
    FROM post
    WHERE id = '你的子帖子ID' -- 替换为实际要查询的子帖ID
    UNION ALL
    -- 递归：向上遍历父帖子
    SELECT p.id, p.content, p.author_id, p.parent_id, pp.depth + 1 AS depth
    FROM post p
    JOIN post_path pp ON p.id = pp.parent_id
)
-- 输出结果，按深度排序控制顺序
SELECT * FROM post_path
ORDER BY depth ASC;

语句说明 #

• 锚点成员：先选中目标子帖子，同时添加depth字段标记层级，初始值为1。
• 递归成员：通过当前记录的parent_id关联父帖子，每次层级加1，直到遍历到根帖（此时parent_id为null，无法继续关联，递归自动终止）。
• 排序控制：ORDER BY depth ASC会按子帖→父帖→根帖的顺序输出；如果需要根帖在前，改为ORDER BY depth DESC即可。

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