1. 云图网首页
  2. 技术专区
  3. 数据库

PostgreSQL：递归查询应用场景

• 数据库

今天在坛子里有人提出了一个问题，问题是这样的：在以下指定表中

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

现在给定一个id号，想得到它完整的名字。如：

  • 当id=7时，名字是：中国辽宁沈阳和平区
  • 当id=5时，名字是：中国辽宁大连

id是任意给定的，不确定在哪一层。递归往上找，直到 fatherid=0 为止。也就是最高层级时结束，求完整SQL语句。

看到这个问题，第一想到的是可以用 PG的递归查询实现，之前也写过类似的例子， https://postgres.fun/20101208135721.html ，只不过是向下递归，而这里的需求是向上递归，略有不同，于是忍不住演示下，这个问题的思路是分两步走，第一步：查询出指定节点的父节点；第二步：将查询出的所有父节点排列到一行。

创建测试表

创建测试表，并插入测试数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
skytf=> create table test_area(id int4,name varchar(32),fatherid int4);  
CREATE TABLE
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);

skytf=>  select  *  From test_area;  
id |name | fatherid  
----+--------+----------  
 1  |  中国 | 0  
 2  |  辽宁 | 1  
 3  |  山东 | 1  
 4  |  沈阳 | 2  
 5  |  大连 | 2  
 6  |  济南 | 3  
 7  |  和平区  | 4  
 8  |  沈河区  | 4  
(8 rows)

查询指定节点以下的所有节点

1
2
3
4
5
6
7
8
9
10
11
12
 
WITH RECURSIVE r AS (
SELECT * FROM test_area WHERE id =  4  
 union ALL  
 SELECT test_area.* FROM test_area, r WHERE test_area.fatherid = r.id  
 )  
SELECT * FROM r ORDER BY id;
id |name | fatherid  
----+--------+----------  
 4  |  沈阳 | 2  
 7  |  和平区  | 4  
 8  |  沈河区  | 4  
(3 rows)

备注：通常的用法是查询指定节点以及指定节点以下的所有节点，那么本贴的需求刚好相反，需要查询指定节点以上的所有节点。

查询指定节点以上的所有节点

1
2
3
4
5
6
7
8
9
10
11
12
 
WITH RECURSIVE r AS (  
SELECT * FROM test_area WHERE id =  4  
 union ALL  
 SELECT test_area.* FROM test_area, r WHERE test_area.id = r.fatherid  
 )  
SELECT * FROM r ORDER BY id;
id | name | fatherid  
----+------+----------  
 1  |  中国  | 0  
 2  |  辽宁  | 1  
 4  |  沈阳  | 2  
(3 rows)

备注：这正是我们想要的结果，接下来需要将 name 字段结果集合并成一行，我这里想到的是创建个function，当然也有其它方法。

创建函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
CREATE or replace FUNCTION func_get_area(in in_id int4,  out o_area text) AS
$$  
DECLARE  
 v_rec_record RECORD;  
BEGIN
o_area =  '';
FOR v_rec_record IN (WITH RECURSIVE r AS (SELECT *  
 FROM test_area  
WHERE id =in_id  
 union ALL  
 SELECT test_area.*  
 FROM test_area, r  
 WHERE test_area.id = r.fatherid)SELECT name  
 FROM r  
 ORDER BY id) LOOP  
o_area := o_area || v_rec_record.name;  
 END LOOP;
 return;  
END;  
$$  
LANGUAGE 'plpgsql';

备注：函数的作用为拼接 name 字段。

执行函数

1
2
3
4
5
6
7
8
9
10
11
 
skytf=>  select func_get_area(7)  ;  
 func_get_area  
--------------------  
中国辽宁沈阳和平区  
(1 row)

skytf=>  select func_get_area(5)  ;  
func_get_area  
---------------  
中国辽宁大连  
(1 row)

备注：正好实现了需求，当表数据量较大时，考虑到性能，建议在表 test_area 字段 id，fatherid 上建立单独的索引。

原创文章，作者：306829225，如若转载，请注明出处：https://blog.ytso.com/237900.html

赞 (0)
0
上一篇 2022年1月29日
下一篇 2022年1月29日

相关推荐

发表回复

登录后才能评论