从邻接表将元素的祖先写入Postgres表

Question

我想将1到n层次结构作为邻接表存储到列出每个元素祖先的表中。我正在使用Postgres数据库（Postgres
10，但是要在其上部署代码的计算机运行Postgres 9.x）。

示例输入表（邻接表）：

INSERT INTO public.test (id, name, parent_id)
VALUES (1, 't', 1),
   (11, 't1', 1),
   (12, 't2', 1),
   (13, 't3', 1),
   (111, 't11', 11),
   (112, 't12', 11),
   (121, 't21', 12),
   (14, 't4', 1),
   (141, 't41', 14),
   (142, 't42', 14)

结果，我想要一个看起来像这样的表（仅显示了几行；此外，我要解决的现实问题有七个层次级别，而不是两个）：

+-----+-------+--------+--------+
| id  | level | level0 | level1 |
+-----+-------+--------+--------+
|   1 |     0 | NULL   | NULL   |
|  11 |     1 | 1      | NULL   |
|  12 |     1 | 1      | NULL   |
| 111 |     2 | 1      | 11     |
+-----+-------+--------+--------+

id是元素的ID，level是该元素在层次结构中所处的级别（0是根级别），level0/1是元素在各个级别上的祖先。

我是SQL的新手，所以我没有任何可以向您显示的代码。Googling告诉我，我可能不太希望使用递归CTE来获得所需的结果并执行自联接，但是我一直无法弄清楚该怎么做。谢谢你的帮助。

编辑

到目前为止，这是我尝试过的：

WITH RECURSIVE cte AS
(
SELECT m.id AS id,
    0 AS level,
    m.parent_id AS level0,
    m.parent_id AS level1,
    m.parent_id AS parent
    FROM public.test AS m
    WHERE m.parent_id IS NULL

UNION ALL

SELECT 
    m.id,
    cte.level + 1,
    cte.parent AS level0,
    cte.parent AS level1,
    m.parent_id AS parent
    FROM public.test AS m 
        INNER JOIN cte
            ON m.parent_id = cte.id 
)
SELECT *
FROM cte;

当然，将level0和设置level1为元素的父元素并不会产生所需的结果，但是我不得不将其设置为某种东西，并且没有比这更进一步的了。

Answer 1

SQL是严格类型化的语言，它不允许a返回的列数SELECT根据所处理的数据而变化。有关讨论，请参见例如将逗号分隔的列数据拆分为其他列。

但是，PostgreSQL提供了一种数组类型，可用于将动态大小的值收集到单个列中。以下递归CTE将每一行的所有祖先收集到这样的数组中：

with recursive rec(id, level, parent_id, ancestors) as (
  select id, 0, parent_id, array[] :: int[]
  from test
  where parent_id = id
  union all
  select t.id, rec.level + 1, t.parent_id, rec.ancestors || array[t.parent_id]
  from test t
  join rec on t.parent_id = rec.id
  where t.parent_id <> t.id
)
select 
  rec.id,
  rec.level,
  rec.ancestors
from rec;

如果对级别有已知限制，则可以从数组的每列中选择元素：

select
  rec.id,
  rec.level,
  rec.ancestors[1] level1,
  rec.ancestors[2] level2,
  ...

SQL小提琴