PostgreSQL樹形結(jié)構(gòu)的遞歸查詢示例

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, '中國(guó)', 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, '綿陽(yáng)', 2, 2);	
insert into demo.tree_data values(6, '武侯區(qū)', 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表示無(wú)限
  [, text branch_delim ])	-- 路徑分隔符

-- 基本用法如下，必須通過(guò)AS子句定義返回的字段名稱和類型
select * 
	from connectby('demo.tree_data', 'id', 'pid', 'sort', '1', 0, '~')
	as (id int, pid int, lvl int, branch text, sort int);
	
-- 查詢結(jié)果
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的相關(guān)信息，如果要查詢code等其他字段，就需要通過(guò)額外的join操作來(lái)實(shí)現(xiàn)。
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 | 中國(guó) | | | 0 | 1
 2 | 四川 | 1 | 中國(guó) | 1 | 1~2
 4 | 成都 | 2 | 四川 | 2 | 1~2~4
 6 | 武侯區(qū) | 4 | 成都 | 3 | 1~2~4~6
 5 | 綿陽(yáng) | 2 | 四川 | 2 | 1~2~5
 3 | 云南 | 1 | 中國(guó) | 1 | 1~3
 7 | 昆明 | 3 | 云南 | 2 | 1~3~7
(7 rows)

-- 
with recursive cte as
(
 -- 先查詢r(jià)oot節(jié)點(diǎn) 
 select
 id, code, pid, '' as pcode,
 code as branch
 from demo.tree_data where id = 1
 union all
 -- 通過(guò)cte遞歸查詢r(jià)oot節(jié)點(diǎn)的直接子節(jié)點(diǎn) 
 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, 
 -- 通過(guò)計(jì)算分隔符的個(gè)數(shù)，模擬計(jì)算出樹形的深度
 (length(branch)-length(replace(branch, '~', ''))) as lvl
from cte;

-- 
 id | code | pid | pcode | branch  | lvl
----+--------+-----+-------+-----------------------+-----
 1 | 中國(guó) | | | 中國(guó)   | 0
 2 | 四川 | 1 | 中國(guó) | 中國(guó)~四川  | 1
 3 | 云南 | 1 | 中國(guó) | 中國(guó)~云南  | 1
 4 | 成都 | 2 | 四川 | 中國(guó)~四川~成都 | 2
 5 | 綿陽(yáng) | 2 | 四川 | 中國(guó)~四川~綿陽(yáng) | 2
 7 | 昆明 | 3 | 云南 | 中國(guó)~云南~昆明 | 2
 6 | 武侯區(qū) | 4 | 成都 | 中國(guó)~四川~成都~武侯區(qū) | 3
(7 rows)

-- step 1 執(zhí)行
 select
 id, code, pid, '' as pcode,
 code as branch
 from demo.tree_data where id = 1
 
-- 結(jié)果集和working table為
 id | code | pid | pcode | branch
----+------+-----+-------+--------
 1 | 中國(guó) | | | 中國(guó)

-- step 2 執(zhí)行遞歸，此時(shí)自引用cte中的數(shù)據(jù)是step 1的結(jié)果
 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
 
 -- 結(jié)果集和working table為
 id | code | pid | pcode | branch 
----+--------+-----+-------+---------------------
 2 | 四川 | 1 | 中國(guó) | 中國(guó)~四川  
 3 | 云南 | 1 | 中國(guó) | 中國(guó)~云南