postgre with函数递归使用

1.建立测试表

drop table if exists org;
create table org (
 id varchar(100),
 name varchar(100),
 pid varchar(100)
); 
insert into org values(1, '中国', null);
insert into org values(2, '四川', 1);
insert into org values(3, '云南', 1);
insert into org values(4, '成都', 2);
insert into org values(5, '绵阳', 2);   
insert into org values(6, '武侯区',4);
insert into org values(7, '昆明', 3); 

2.查询

with recursive rec as (
    -- 先查询root节点,非递归部分
    select id,pid,name ,name as branch from org where id='1'
    union all 
    -- 通过rec递归查询root节点的直接子节点 ,::varchar(100)是显示转化查询结果,递归部分
    select o1.id,o1.pid,o1.name,(c.branch||'/'||o1.name ) ::varchar(100)
  from org o1 inner join rec c on c.id = o1.pid
)select id,name,pid,branch  from rec

------------------------------------
id    | name    | pid    | branch |
------------------------------------
1    中国                 中国
2    四川            1    中国/四川
3    云南            1    中国/云南
4    成都            2    中国/四川/成都
5    绵阳            2    中国/四川/绵阳
7    昆明            3    中国/云南/昆明
6    武侯区        4    中国/四川/成都/武侯区

 

执行过程说明

从上面的例子可以看出,WITH RECURSIVE语句包含了两个部分

  • non-recursive term(非递归部分),即上例中的union all前面部分
  • recursive term(递归部分),即上例中union all后面部分

执行步骤如下

  • 执行non-recursive term。(如果使用的是union而非union all,则需对结果去重)其结果作为recursive term中对result的引用,同时将这部分结果放入临时的working table中
  • 重复执行如下步骤,直到working table为空:用working table的内容替换递归的自引用,执行recursive term,(如果使用union而非union all,去除重复数据),并用该结果(如果使用union而非union all,则是去重后的结果)替换working table
posted @ 2020-02-07 20:50  君子笑而不语  阅读(695)  评论(0编辑  收藏  举报