PostgreSQL递归查询获取祖父节点对应末端子节点数据问题

我需要获取祖父数据对应的末端子节点数据，现有两张表：一张是存储父子数据的data_master主表，另一张是存储节点关联关系的data_relation表。

基于上述数据，需要查询得到父节点数据及其对应的末端子节点数据，预期输出如下：

该查询最终需要集成到Java Batch中使用，业务逻辑为：分别传入child_data值331和327时，返回对应的匹配结果。

原查询代码如下：

@set ko_id = '331'

select parent_id,child_id,count(parent_id) from (
 WITH RECURSIVE ancestors (parent_id) AS (
  SELECT distinct t.parent_id ,t.parent_id as extra_id,t.child_id , msok.data_type -- and find all its ancestors
  FROM public.data_relation AS t 
    JOIN data_relation AS a ON t.child_id = a.parent_id or t.child_id = a.child_id 
    left join data_master msok on msok.id = t.parent_id
  where a.child_id = :ko_id
), 
descendants (parent_id) AS (
  SELECT  parent_id ,extra_id as extra_id,child_id, data_type FROM ancestors  
  UNION ALL
  SELECT t.child_id,d.parent_id as extra_id,t.child_id, msok.data_type -- and find all their descendants
  FROM public.data_relation AS t 
    JOIN descendants AS d ON t.parent_id = d.parent_id
    left join data_master msok on msok.id = t.child_id 
) 
SELECT 
  parent_id, extra_id, child_id, data_type
FROM 
  descendants where data_type ='1') abc group by parent_id,child_id

原代码问题点 #

• 递归CTE字段定义不匹配：ancestors CTE声明仅包含parent_id一个字段，但实际查询返回4个字段，存在语法错误
• 初始查询关联逻辑冗余：data_relation自连接的条件t.child_id = a.parent_id or t.child_id = a.child_id会产生大量无效匹配，无法正确向上追溯祖父节点
• 缺少末端节点判断逻辑：递归没有终止条件，可能出现死循环，且最后统一过滤data_type='1'会漏掉有效匹配项
• 递归方向逻辑混乱：向上查祖先和向下查后代的关联逻辑写反，导致无法匹配到正确的父子链路

修复后查询代码 #

-- 入参：:ko_id 为传入的子节点值，如331、327
WITH RECURSIVE ancestor_chain AS (
    -- 向上递归查询传入节点的所有上级父节点
    SELECT 
        dr.parent_id,
        dr.child_id,
        1 AS hierarchy_level
    FROM public.data_relation dr
    WHERE dr.child_id = :ko_id

    UNION ALL

    SELECT 
        dr_upper.parent_id,
        dr_upper.child_id,
        ac.hierarchy_level + 1
    FROM public.data_relation dr_upper
    INNER JOIN ancestor_chain ac ON dr_upper.child_id = ac.parent_id
),
top_ancestor AS (
    -- 获取最顶层的祖父节点（递归到无上级的节点）
    SELECT parent_id AS top_parent_id
    FROM ancestor_chain
    ORDER BY hierarchy_level DESC
    LIMIT 1
),
descendant_chain AS (
    -- 从祖父节点开始向下递归查询所有子节点
    SELECT 
        ta.top_parent_id,
        dr.child_id,
        dm.data_type
    FROM top_ancestor ta
    INNER JOIN public.data_relation dr ON dr.parent_id = ta.top_parent_id
    LEFT JOIN public.data_master dm ON dr.child_id = dm.id

    UNION ALL

    SELECT 
        dc.top_parent_id,
        dr_next.child_id,
        dm_next.data_type
    FROM descendant_chain dc
    INNER JOIN public.data_relation dr_next ON dr_next.parent_id = dc.child_id
    LEFT JOIN public.data_master dm_next ON dr_next.child_id = dm_next.id
)
-- 过滤出末端子节点（data_type=1 且没有子节点）
SELECT DISTINCT
    top_parent_id AS parent_id,
    child_id AS leaf_child_id
FROM descendant_chain
WHERE data_type = '1'
AND child_id NOT IN (SELECT DISTINCT parent_id FROM public.data_relation)

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