背景
處理不確定深度的層級結(jié)構(gòu)，比如組織機(jī)構(gòu)，一個(gè)常用的設(shè)計(jì)是在一張表里面保存 ID 和 Parent_ID ，并且通過自聯(lián)結(jié)的辦法構(gòu)造一顆樹。這種方式對寫數(shù)據(jù)的過程很友好，但是查詢過程就變得相對復(fù)雜。在不引入MPTT模型的前提下，必須通過遞歸算法來查詢某個(gè)節(jié)點(diǎn)和下級子節(jié)點(diǎn)。
Oracle提供的connect by擴(kuò)展語法，簡單好用。但是其他的RDBMS就沒這么人性化了（或者我不知道）。最近在項(xiàng)目中使用PostgreSQL來查詢樹形數(shù)據(jù)，記錄一下。
構(gòu)造樣本數(shù)據(jù)
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, '武侯區(qū)', 4, 1);
insert into demo.tree_data values(7, '昆明', 3, 1);
connectby函數(shù)
如果安裝了 tablefunc 擴(kuò)展，就可以使用PG版本的connectby函數(shù)。這個(gè)沒有Oracle那么強(qiáng)大，但是可以滿足基本要求。
-- 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);
-- 查詢結(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等其他字段，就需要通過額外的join操作來實(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 | 中國 | | | 0 | 1
2 | 四川 | 1 | 中國 | 1 | 1~2
4 | 成都 | 2 | 四川 | 2 | 1~2~4
6 | 武侯區(qū) | 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)
PS：雖然通過join可以查詢出節(jié)點(diǎn)的code，但是branch部分不能直接轉(zhuǎn)換成對應(yīng)的code，使用上還是不太方便。
CTE語法
使用CTE語法，通過 with recursive 來實(shí)現(xiàn)樹形數(shù)據(jù)的遞歸查詢。這個(gè)方法雖然沒有connectby那么直接，但是靈活性和顯示效果更好。
--
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
-- 通過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,
-- 通過計(jì)算分隔符的個(gè)數(shù)，模擬計(jì)算出樹形的深度
(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 | 武侯區(qū) | 4 | 成都 | 中國~四川~成都~武侯區(qū) | 3
(7 rows)
執(zhí)行過程說明
從上面的例子可以看出，WITH RECURSIVE語句包含了兩個(gè)部分
- non-recursive term（非遞歸部分），即上例中的union all前面部分
- recursive term（遞歸部分），即上例中union all后面部分
執(zhí)行步驟如下
- 執(zhí)行non-recursive term。（如果使用的是union而非union all，則需對結(jié)果去重）其結(jié)果作為recursive term中對result的引用，同時(shí)將這部分結(jié)果放入臨時(shí)的working table中
- 重復(fù)執(zhí)行如下步驟，直到working table為空：用working table的內(nèi)容替換遞歸的自引用，執(zhí)行recursive term，（如果使用union而非union all，去除重復(fù)數(shù)據(jù)），并用該結(jié)果（如果使用union而非union all，則是去重后的結(jié)果）替換working table
以上面的query為例，來看看具體過程
執(zhí)行non-recursive query
-- 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 | 中國 | | | 中國
執(zhí)行recursive query
-- 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 | 中國 | 中國~四川
3 | 云南 | 1 | 中國 | 中國~云南
3、繼續(xù)執(zhí)行recursive query，直到結(jié)果集和working table為空
4、結(jié)束遞歸，將前三個(gè)步驟的結(jié)果集合并，即得到最終的WITH RECURSIVE的結(jié)果集。
嚴(yán)格來講，這個(gè)過程實(shí)現(xiàn)上是一個(gè)迭代的過程而非遞歸，不過RECURSIVE這個(gè)關(guān)鍵詞是SQL標(biāo)準(zhǔn)委員會(huì)定立的，所以PostgreSQL也延用了RECURSIVE這一關(guān)鍵詞。
總結(jié)
以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，謝謝大家對腳本之家的支持。
您可能感興趣的文章:- PostgreSQL圖(graph）的遞歸查詢實(shí)例
- 在PostgreSQL中實(shí)現(xiàn)遞歸查詢的教程
- PostgreSQL利用遞歸優(yōu)化求稀疏列唯一值的方法
