PostgreSQL:递归查询应用场景
今天在坛子里有人提出了一个问题,问题是这样的:在以下指定表中 id name fatherid 现在给定一个id号,想得到它完整的名字。如: id是任意给定的,不确定在哪一层。递归往上找,直到 fatherid=0 为止。也就是最高层级时结束, 看到这个问题,第一想到的是可以用 PG的递归查询实现,之前也写过类似的例子, 这个问题的思路是分两步走,第一步:查询出指定节点的父节点;第二步:将查询出的所有父节点排列到一行。 --1 创建测试表,并插入测试数据 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) --2 查询指定节点以下的所有节点 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) 备注:通常的用法是查询指定节点以及指定节点以下的所有节点,那么本贴的需求刚好相反,需要查询指定节点以上的所有节点。 --3 查询指定节点以上的所有节点 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,当然也有其它方法。 --4 create funcion 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 字段。 --5 测试 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 上建立单独的索引。 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |