PostgreSQL如何实现类似Oracle中的家族树功能

先来设计一下表结构：

create table city(id varchar(3) , pid varchar(3) , name varchar(10));

下面我们造几条测试数据：

INSERT INTO city values('001','0','宁波市');
INSERT INTO city values('002','001','镇海区');
INSERT INTO city values('003','001','北仑区');
INSERT INTO city values('004','001','江北区');
INSERT INTO city values('005','002','骆驼街道');
INSERT INTO city values('006','002','招宝山街道');
INSERT INTO city values('007','002','蛟川街道');
INSERT INTO city values('007','003','小港街道');

下面是递归查询生成树状结构的查询语名：

with recursive t(id,name,pid,path,depth) as(
select id,name,pid,id::text path,1 depth from city where pid='0'
union
select a.id,a.name,a.pid,b.path||a.id path,b.depth+1 depth from city a,t b where a.pid=b.id
)select * from t order by path;

最终效果如下：

 

递归原理如下：
第一步，会得到 select id,name,pid,id::text path,1 depth from city where pid='0' 一条记录，并放在t中
第二步，再执行 select a.id,a.name,a.pid,b.path||a.id path,b.depth+1 depth from city a,t b where a.pid=b.id 放入t中,
之后不断循环第二步直到条件无法匹配。