PostgreSQL 递归查询

发表于 | 分类于 |

PostgreSQL 递归查询

示例数据

id name fatherid
1 中国 0
2 辽宁 1
3 山东 1
4 沈阳 2
5 大连 2
6 济南 3
7 和平区 4
8 沈河区 4

查询规则

当给定一个id时能得到它完整的地名，例如当id=7时，地名是：中国辽宁沈阳和平区，当id=5时，地名是：中国辽宁大连，这是一个典型的层次数据递归应用场景。

PostgreSQL实现

创建示例数据

1
2
3
4
5
6
7
8
9
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, '沈阳'   ,2);
INSERT INTO test_area VALUES (5, '大连'   ,2);
INSERT INTO test_area VALUES (6, '济南'   ,3);
INSERT INTO test_area VALUES (7, '和平区' ,4);
INSERT INTO test_area VALUES (8, '沈河区' ,4);

查询

1
2
3
4
5
6
7
8
9
10
11
12
13
with RECURSIVE t as
  (select *
   from test_area
   where id=8
   union all select test_area.*
   from test_area,
        t
   where t.fatherid=test_area.id )
select string_agg(name, '')
from
  (select name
   from t
   order by id) foo

结果

中国辽宁沈阳沈河区

RECURSIVE：引用自己的输出
string_agg: PG聚合函数，类比Oracle的listagg

Oracle实现

创建示例数据

1
2
3
4
5
6
7
8
9
CREATE TABLE test_area(id int,name varchar(32),fatherid int);
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, '沈阳'   ,2);
INSERT INTO test_area VALUES (5, '大连'   ,2);
INSERT INTO test_area VALUES (6, '济南'   ,3);
INSERT INTO test_area VALUES (7, '和平区' ,4);
INSERT INTO test_area VALUES (8, '沈河区' ,4);

查询

1
2
3
4
5
6
select listagg(name, '')  within 
group (
       order by id) as name
from test_area
start with id=8 connect by
prior FATHERID=id

结果

中国辽宁沈阳沈河区