Postgres递归CTE实现pilates_bill表祖先链查询

刚好处理过类似的自连接表递归查询需求，给你整理了能直接运行的SQL代码，完美适配你的pilates_bill表结构：

WITH RECURSIVE chain(from_id, to_id, ancestor_chain) AS (
    -- 初始递归入口：指定起始账单ID，这里以1为例，先把起始节点加入链
    SELECT NULL::integer, bill_id, ARRAY[bill_id]
    FROM pilates_bill
    WHERE bill_id = 1
    UNION ALL
    -- 递归遍历逻辑：找到当前节点的父节点，追加到祖先链中
    SELECT c.to_id, p.previous_bill_id, c.ancestor_chain || p.previous_bill_id
    FROM chain c
    JOIN pilates_bill p ON c.to_id = p.bill_id
    WHERE p.previous_bill_id IS NOT NULL
)
-- 按需输出结果：可以选择完整链关系，或者单独提取祖先列表
SELECT 
    to_id AS current_bill_id, 
    from_id AS parent_bill_id, 
    ancestor_chain AS full_ancestor_list
FROM chain;

代码拆解说明： #

• 初始成员：我们从目标起始账单（这里是ID=1）开始初始化，from_id设为NULL（因为起始节点没有父节点），用数组ancestor_chain来存储整条祖先链，初始值只有起始节点自己。
• 递归成员：每次递归都会把当前节点的父节点（previous_bill_id）关联出来，把父节点ID追加到链数组里，同时更新节点关系，直到遍历到没有父节点（previous_bill_id IS NULL）的顶层节点为止。
• 结果输出：你可以根据需求调整输出字段，比如只需要所有祖先ID的话，用unnest(ancestor_chain)就能把数组拆成单独的行。

示例结果（对应你的数据）： #

用你给出的1→2，2→3，3→4，5→NULL数据，执行后会得到：

如果只需要提取从1出发的所有祖先ID列表，用这个简化查询：

SELECT unnest(ancestor_chain) AS ancestor_bill_id
FROM chain
WHERE to_id = (SELECT MAX(to_id) FROM chain);

输出结果就是：1、2、3、4。

灵活调整： #

要更换起始节点的话，只需要修改初始成员里的WHERE bill_id = 1为你需要的ID，比如换成5的话，结果就只有{5}，因为它没有父节点。

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