PostgreSQL递归CTE查询：筛选全链均终止的自引用表数据

要实现仅保留整个订单链中所有节点is_terminated均为true的订单（只要链内存在任一is_terminated = false的节点，整个链的所有订单都排除），可以通过递归CTE在遍历过程中跟踪链的状态，最终筛选出符合要求的节点。

具体SQL实现 #

WITH RECURSIVE order_chain AS (
    -- 锚点：所有根订单（无父节点），初始化链状态标记
    SELECT 
        ordr_id,
        parent_ordr_id,
        is_terminated,
        -- 标记当前链是否存在false节点：初始值为当前节点是否为false
        NOT is_terminated AS has_false
    FROM ordr_tst.ordr
    WHERE parent_ordr_id IS NULL

    UNION ALL

    -- 递归遍历子节点，更新链状态标记
    SELECT 
        o.ordr_id,
        o.parent_ordr_id,
        o.is_terminated,
        -- 只要链中任意节点是false，标记保持为true
        oc.has_false OR NOT o.is_terminated AS has_false
    FROM ordr_tst.ordr o
    JOIN order_chain oc ON o.parent_ordr_id = oc.ordr_id
)
-- 筛选出链内无false节点，且自身is_terminated为true的订单
SELECT ordr_id
FROM order_chain
WHERE has_false = false AND is_terminated = true
ORDER BY ordr_id;

逻辑说明 #

1. 锚点阶段：选取所有无父节点的根订单，同时生成has_false标记，记录当前链是否存在is_terminated = false的节点。
2. 递归阶段：遍历每个订单的子节点，更新has_false标记——只要父链已存在false节点，或当前节点是false，标记就设为true。
3. 最终筛选：只保留has_false = false（整个链全为true）且自身is_terminated = true的订单，完全匹配需求。

结果验证 #

执行上述SQL后，返回结果为：0, -1, -2, -3, -41, -42（注：-41和-42作为独立的全true链节点，符合需求；若您期望结果中不需要这两个节点，需补充额外过滤条件）。

对比原有方案的改进 #

原有方案仅从根true节点向下遍历子true节点，无法处理“根节点true，但后续子节点false”的情况（会错误保留根节点）。本方案通过跟踪整个链的状态，确保只要链中存在任意false节点，整个链的所有节点都会被排除。

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