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