PostgreSQL如何实现带动态WHERE子句的递归查询批量执行

核心实现逻辑如下：

• 在递归CTE中新增root_mother字段，跟踪每个递归分支对应的初始根节点，即my_table中去重的cadastral_reference值，替代原固定的单一根节点过滤条件
• 递归初始段从所有去重的地籍参考值出发匹配关联关系，遍历全量层级
• 最后分组聚合时过滤出末端子级，无后代的根节点自动返回空数组

可直接执行的SQL语句：

WITH RECURSIVE q_filiation (root_mother, current_mother, daughter) AS (
    -- 递归初始节点：匹配所有根节点的第一层子级
    SELECT 
        t.cadastral_reference AS root_mother,
        f.mother AS current_mother,
        f.daughter
    FROM (SELECT DISTINCT cadastral_reference FROM my_table) t
    LEFT JOIN my_table_filiation f ON f.mother = t.cadastral_reference
  UNION ALL
    -- 递归遍历：查询所有子级的后代，保留原始根节点标识
    SELECT 
        f.root_mother,
        p.mother AS current_mother,
        p.daughter
    FROM q_filiation f
    INNER JOIN my_table_filiation p ON p.mother = f.daughter
),
-- 筛选末端节点：未作为母节点出现在关联表中的子级
end_nodes AS (
    SELECT DISTINCT daughter 
    FROM my_table_filiation
    WHERE daughter NOT IN (SELECT DISTINCT mother FROM my_table_filiation)
)
-- 分组聚合得到最终结果
SELECT
    ARRAY[q.root_mother] AS mother,
    ARRAY_AGG(DISTINCT q.daughter) FILTER (WHERE q.daughter IN (SELECT daughter FROM end_nodes)) AS last_daughter
FROM q_filiation q
GROUP BY q.root_mother
ORDER BY q.root_mother;

如果你的数据量较大，建议给my_table_filiation的mother、daughter字段添加索引，可大幅提升递归查询的执行效率。

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