postgressql中单表树结构的递归查询

在单表中用pid字段表示父节点id，查询时要求根据id查找某节点及其所有子节点。

测试的表结构如下：

create table test2(
  id integer,
  name varchar,
  pid integer
);

 

插入测试数据

 

insert into test2 values(1,'A',null);
insert into test2 values(2,'A1',1);
insert into test2 values(3,'A2',1);
insert into test2 values(4,'B',2);

 树结构如下：

1    A

2    ----A1

3    --------B

4    ----A2

根据节点ID查父节点及其所有子节点的简单查询（例如查id=2的节点）

with recursive t(id, name, pid) as (
    select id, name, pid from test2 where id=2
    union all
    select t2.id, t2.name, t2.pid from test2 t2
    join t on t2.pid=t.id
)
select id, name, pid from t order by id;

 带路径及树节点深度的查询

with recursive t(id, name, pid, path, depth) as (
    select id, name, pid, array[id] as path, 1 as depth
    from test2 where pid is null
    union all
    select t2.id, t2.name, t2.pid, t.path || t2.id, t.depth + 1 as depth
    from test2 t2
    join t on t2.pid=t.id
)
select id, name, pid, path, depth from t order by path;