PostgreSQL树形结构的递归查询示例

drop table if exists demo.tree_data;

create table demo.tree_data (

  id integer ,

  code text,

  pid integer ,

  sort integer

);

insert into demo.tree_data values (1, '中国' , null , 1);

insert into demo.tree_data values (2, '四川' , 1, 1);

insert into demo.tree_data values (3, '云南' , 1, 2);

insert into demo.tree_data values (4, '成都' , 2, 1);

insert into demo.tree_data values (5, '绵阳' , 2, 2);  

insert into demo.tree_data values (6, '武侯区' , 4, 1);

insert into demo.tree_data values (7, '昆明' , 3, 1);  

-- API 如下

connectby(text relname,             -- 表名称

   text keyid_fld,           -- id字段

   text parent_keyid_fld     -- 父id字段   

   [, text orderby_fld ],    -- 排序字段

   text start_with,          -- 起始行的id值

   int max_depth             -- 树深度，0表示无限

   [, text branch_delim ])   -- 路径分隔符

-- 基本用法如下，必须通过AS子句定义返回的字段名称和类型

select *

     from connectby( 'demo.tree_data' , 'id' , 'pid' , 'sort' , '1' , 0, '~' )

     as (id int , pid int , lvl int , branch text, sort int );

-- 查询结果

id | pid | lvl | branch | sort

----+-----+-----+---------+------

  1 | | 0 | 1 | 1

  2 | 1 | 1 | 1~2 | 2

  4 | 2 | 2 | 1~2~4 | 3

  6 | 4 | 3 | 1~2~4~6 | 4

  5 | 2 | 2 | 1~2~5 | 5

  3 | 1 | 1 | 1~3 | 6

  7 | 3 | 2 | 1~3~7 | 7

(7 rows )

-- 仅仅使用基本用法，只能查询出id的相关信息，如果要查询code等其他字段，就需要通过额外的join操作来实现。

select

     t.id, n.code, t.pid, p.code as pcode, lvl, branch

from (

     select * from connectby( 'demo.tree_data' , 'id' , 'pid' , 'sort' , '1' , 0, '~' )

         as (id int , pid int , lvl int , branch text, sort int )

) as t

     left join demo.tree_data as n on (t.id = n.id)

     left join demo.tree_data as p on (t.pid = p.id)

order by t.sort ;  

  id | code | pid | pcode | lvl | branch

----+--------+-----+-------+-----+---------

  1 | 中国 | | | 0 | 1

  2 | 四川 | 1 | 中国 | 1 | 1~2

  4 | 成都 | 2 | 四川 | 2 | 1~2~4

  6 | 武侯区 | 4 | 成都 | 3 | 1~2~4~6

  5 | 绵阳 | 2 | 四川 | 2 | 1~2~5

  3 | 云南 | 1 | 中国 | 1 | 1~3

  7 | 昆明 | 3 | 云南 | 2 | 1~3~7

(7 rows )

--

with recursive cte as

(

  -- 先查询root节点

  select

  id, code, pid, '' as pcode,

  code as branch

  from demo.tree_data where id = 1

  union all

  -- 通过cte递归查询root节点的直接子节点

  select

  origin.id, origin.code, cte.id as pid, cte.code as pcode,

  cte.branch || '~' || origin.code

  from cte

  join demo.tree_data as origin on origin.pid = cte.id

)

select

  id,code, pid, pcode, branch,

  -- 通过计算分隔符的个数，模拟计算出树形的深度

  (length(branch)-length( replace (branch, '~' , '' ))) as lvl

from cte;

--

  id | code | pid | pcode | branch  | lvl

----+--------+-----+-------+-----------------------+-----

  1 | 中国 | | | 中国   | 0

  2 | 四川 | 1 | 中国 | 中国~四川  | 1

  3 | 云南 | 1 | 中国 | 中国~云南  | 1

  4 | 成都 | 2 | 四川 | 中国~四川~成都 | 2

  5 | 绵阳 | 2 | 四川 | 中国~四川~绵阳 | 2

  7 | 昆明 | 3 | 云南 | 中国~云南~昆明 | 2

  6 | 武侯区 | 4 | 成都 | 中国~四川~成都~武侯区 | 3

(7 rows )

-- step 1 执行

  select

  id, code, pid, '' as pcode,

  code as branch

  from demo.tree_data where id = 1

-- 结果集和working table为

  id | code | pid | pcode | branch

----+------+-----+-------+--------

  1 | 中国 | | | 中国

-- step 2 执行递归，此时自引用cte中的数据是step 1的结果

  select

  origin.id, origin.code, cte.id as pid, cte.code as pcode,

  cte.branch || '~' || origin.code

  from cte

  join demo.tree_data as origin on origin.pid = cte.id

  -- 结果集和working table为

  id | code | pid | pcode | branch

----+--------+-----+-------+---------------------

  2 | 四川 | 1 | 中国 | 中国~四川 

  3 | 云南 | 1 | 中国 | 中国~云南