postgresql with recursive 递归实现省市区分类查询

递归查询使用CTE

WITH查询的一个重要属性是RECURSIVE，使用RECURSIVE属性可以引用自己的输出，从而实现递归，一般用于层次结构或树状结构的应用场景。

例子如下：

id name  fatherid
1 中国 0
2 湖南省 1
3 广东省 1
4 海南省 1
5 河北省 1
6 河南省 1
7 山东省 1
8 湖北省 1
9 江苏省 1
10 深圳市 3
11 长沙市 2
12 南山区 10
13 衡阳市 2
14 耒阳市 13
15 龙岗区 10
16 福田区 10
17 宝安区 10
...

当给定一个ID时能得到它完整的地名，例如当id=7时，地名是：中国广东省深圳市福田区，当ID=16时，地名是：中国广东省深圳市福田区，这是一个层次数据递归应用场景，可以通过PostgreSQL的WITH查询实现，首先创建表并插入数据，如下：

create table test_area(id int4,name varchar(32),fatherid int4);

insert into test_area values (1,'中国',0);
insert into test_area values (2,'湖南省',1);
insert into test_area values (3,'广东省',1);
insert into test_area values (4,'海南省',1);
insert into test_area values (5,'河北省',1);
insert into test_area values (6,'河南省',1);
insert into test_area values (7,'山东省',1);
insert into test_area values (8,'湖北省',1);
insert into test_area values (9,'江苏省',1);
insert into test_area values (10,'深圳市',3);
insert into test_area values (11,'长沙市',2);
insert into test_area values (12,'南山区',10);
insert into test_area values (13,'衡阳市',2);
insert into test_area values (14,'耒阳市',13);
insert into test_area values (15,'龙岗区',10);
insert into test_area values (16,'福田区',10);
insert into test_area values (17,'宝安区',10);

使用PostgreSQL的with查询检索id为7及以上的所有父节点，如下：

with recursive r as (

select * from test_area where id=16

union all

select test_area.* from test_area,r where test_area.id=r.fatherid

)

select * from r order by id;

查询结果如下：

接下来将结果的name字段合并成“中国广东省深圳市福田区”，可使用php foreach函数遍历实现，这里通过string_agg函数实现，如下：

with recursive r as (

select * from test_area where id=16

union all

select test_area.* from test_area,r where test_area.id=r.fatherid

)

select string_agg(name,'') from (select name from r order by id) n;

结果如下：

本文知识学习自《PostgreSQL实战》page73　74