postgresql - 生成从每个节点到表示为表中边的树的根的路径

Question

我在 PostgreSQL 数据库上有下表(parent_fk 是引用同一个表的外键):

id    |    parent_fk
72    |    
342   |    72
583   |    342

我想查询这个表并通过中间父子关系发现每个元素到最终父元素的路径。例如，我想获取以下内容作为 SQL 查询的答案:

id    |    parent_fk    |    path
72    |                 |     72
342   |    72           |    72;342
583   |    342          |   72;342;583

我在 PostgreSQL 上阅读了 CTE(通用表表达式)和递归查询，但我自己还不能解决这个问题。有任何想法吗？提前致谢。

Answer 1

您可能想要检查 ltree contrib module如果您正在做很多此类事情。

这是一个可以完成这项工作的 CTE，请参阅 SQLFiddle :

WITH RECURSIVE x(id,parent_fk,parents,last_id, depth) AS (
  SELECT id, parent_fk, ARRAY[id] AS parents, id AS last_id, 0 AS depth FROM table1
  UNION ALL
  SELECT x.id, x.parent_fk, parents||t1.parent_fk, t1.parent_fk AS last_id, x.depth + 1
  FROM x 
    INNER JOIN table1 t1 
    ON (last_id= t1.id)
  WHERE t1.parent_fk IS NOT NULL
)
SELECT id, parent_fk, array_to_string(parents,';')
FROM x 
WHERE depth = (SELECT max(sq.depth) FROM x sq WHERE sq.id = x.id);

您的表是一个有向图的表示，作为一组边。您已经指定该图是一棵树，这意味着它是非循环的。您想要做的是找到从树上每个节点(内部或叶子)到根的路径，并将其表示为分号分隔的字符串。