您需要递归查询：

WITH RECURSIVE cte AS (
SELECT node_id, parent_id AS ancestor_id
  FROM your_table
 WHERE parent_id is not null
UNION ALL
SELECT children.node_id, parent.parent_id
  FROM cte AS chidren
 INNER JOIN your_table AS parent
    ON parent.node_id = children.parent_id
)
SELECT *
  FROM cte ;

如果在node_id =〉paren_id树中有循环，这个查询可能不会停止。为了避免递归查询中出现无限循环，必须添加一个测试：

WITH RECURSIVE cte AS (
SELECT node_id, parent_id AS ancestor_id, array[node_id, parent_id] AS check
  FROM your_table
 WHERE parent_id is not null
UNION ALL
SELECT children.node_id, parent.parent_id, children.check || parent.parent_id
  FROM cte AS chidren
 INNER JOIN your_table AS parent
    ON parent.node_id = children.parent_id
 WHERE NOT children.check @> array[parent.parent_id]  -- this condition will avoid infinite loops
)
SELECT node_id, ancestor_id
  FROM cte ;