sql - 从 CTE 创建的动态表(父/子)

Question

如果我有一个名为tree的非常简单的表

create table if not exists tree (id int primary key, parent int, name text);

还有几行数据

insert into tree values (1, null, 'A');
insert into tree values (2, 1, 'B');
insert into tree values (3, 1, 'C');
insert into tree values (4, 2, 'D');
insert into tree values (5, 2, 'E');
insert into tree values (6, 3, 'F');
insert into tree values (7, 3, 'G');

我可以轻松地在其上运行 CTE，并生成一个输出给我这样的路径

with recursive R(id, level, path, name) as (
    select id,1,name,name from tree where parent is null
    union select tree.id, level + 1, path || '.' || tree.name, tree.name from tree join R on R.id=tree.parent
) select level,path,name from R;

给出输出

level  | path  | name 
-------+-------+------
     1 | A     | A
     2 | A.B   | B
     2 | A.C   | C
     3 | A.B.D | D
     3 | A.B.E | E
     3 | A.C.F | F
     3 | A.C.G | G

我想知道的是，是否有可能以某种方式将此输出投影到另一个表中，根据级别(级别 1、级别 2、级别 3 等)动态创建列，给我这样的返回

id | level1 | level2 | level3
---+--------+--------+-------
 1 | A      |        |
 2 | A      | B      |
 3 | A      | C      |
 4 | A      | B      | D
 5 | A      | B      | E
 6 | A      | C      | F
 7 | A      | C      | G

如有任何帮助，我们将不胜感激。

Answer 1

如果您知道树的最大深度，我会保留您的方法并使用数组串联简化它以产生所需的输出。所以对于 5 级树，它看起来像这样:

WITH RECURSIVE R(id, path) AS (
    SELECT id, ARRAY[name::text] FROM tree WHERE parent IS NULL
    UNION SELECT tree.id, path || tree.name FROM tree JOIN R ON R.id=tree.parent
) 
SELECT id,
    path[1] AS l1, 
    path[2] AS l2, 
    path[3] AS l3, 
    path[4] AS l4, 
    path[5] AS l5
FROM R;

PS:很抱歉没有对 Ziggy 非常接近的回答发表评论，但我没有足够的声誉来这样做。我不明白你为什么需要一个窗口函数？