PostgreSQL 11递归查询返回错误结果，误包含无关单元

推荐方案：直接从目标节点递归（高效且精准） #

这种方式无需遍历全树，直接从UN-25开始递归获取其自身及所有子节点，彻底避免误匹配问题：

WITH RECURSIVE unit_tree AS (
    -- 起始节点：UN-25自身
    SELECT 
        u.id, 
        u.parentid, 
        ul.name AS node_name, 
        u.status, 
        u.active,
        CAST(u.id AS TEXT) AS path
    FROM unit u
    LEFT JOIN unitlang ul ON ul.unit_id = u.id AND ul.lang = 'EN'
    WHERE u.id = 'UN-25' AND u.status = '1'
    
    UNION ALL
    
    -- 递归获取所有子节点
    SELECT 
        u.id, 
        u.parentid, 
        ul.name AS node_name, 
        u.status, 
        u.active,
        CAST(ut.path || ',' || u.id AS TEXT) AS path
    FROM unit_tree ut
    JOIN unit u ON u.parentid = ut.id
    LEFT JOIN unitlang ul ON ul.unit_id = u.id AND ul.lang = 'EN'
    WHERE u.status = '1'
)
SELECT id FROM unit_tree;

原查询问题分析 #

原查询通过position('UN-25' in path)过滤结果，会误匹配包含"UN-25"子串的id（比如UN-255的字符串本身包含"UN-25"），即使该节点不在UN-25的层级下，导致错误结果。

基于原查询结构的修正方案 #

如果必须保留全树遍历的逻辑，可通过包裹逗号的方式确保匹配完整节点id：

select id from unit where id in (
   select distinct id    
   from    
   (WITH RECURSIVE tree    
   AS    
   (    
     SELECT    
         ul.name as node_name, unit.id, unit.parentid as parent_id,  
     cast(unit.id AS text) AS path,unit.status as status,unit.active as active    
      FROM unit    
     left outer join unitlang ul on ul.unit_id=unit.id and ul.lang = 'EN'   
     WHERE unit.parentid IS NULL  
     and unit.status='1'     

       UNION    
      SELECT    
          node_name, f1.id, f1.parentid,cast(tree.path || ',' || f1.id AS text) AS path,f1.status as status,f1.active as active    
      FROM    
         tree    
         JOIN unit f1 ON f1.parentid = tree.id    
        where f1.status='1'   
    )    
   SELECT id, node_name, parent_id, node_name, path FROM tree ORDER BY path) liste    
   -- 修改为匹配完整节点的过滤条件
   where position(',' || 'UN-25' || ',' in ',' || path || ',') <> 0 
     and POSITION(',' || id || ',' in ',' || path || ',') >= POSITION(',' || 'UN-25' || ',' in ',' || path || ',')    
)

通过在path和目标id前后添加逗号，确保匹配的是独立的节点id（比如,UN-25,），不会误判包含该子串的其他id。

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