背景
處理不確定深度的層級結構，比如組織機構，一個常用的設計是在一張表里面保存 ID 和 Parent_ID ，并且通過自聯結的辦法構造一顆樹。這種方式對寫數據的過程很友好，但是查詢過程就變得相對復雜。在不引入MPTT模型的前提下，必須通過遞歸算法來查詢某個節點和下級子節點。
Oracle提供的connect by擴展語法，簡單好用。但是其他的RDBMS就沒這么人性化了（或者我不知道）。最近在項目中使用PostgreSQL來查詢樹形數據，記錄一下。
構造樣本數據
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
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); |
connectby函數
如果安裝了 tablefunc 擴展，就可以使用PG版本的connectby函數。這個沒有Oracle那么強大，但是可以滿足基本要求。
|
1
2
3
4
5
6
7
8
|
-- 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 ]) -- 路徑分隔符 |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
-- 基本用法如下，必須通過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) |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
-- 僅僅使用基本用法，只能查詢出id的相關信息，如果要查詢code等其他字段，就需要通過額外的join操作來實現。select t.id, n.code, t.pid, p.code as pcode, lvl, branchfrom ( 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) |
PS：雖然通過join可以查詢出節點的code，但是branch部分不能直接轉換成對應的code，使用上還是不太方便。
CTE語法
使用CTE語法，通過 with recursive 來實現樹形數據的遞歸查詢。這個方法雖然沒有connectby那么直接，但是靈活性和顯示效果更好。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
-- 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 lvlfrom 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) |
執行過程說明
從上面的例子可以看出，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
以上面的query為例，來看看具體過程
執行non-recursive query
|
1
2
3
4
5
6
7
8
9
10
|
-- 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 | 中國 | | | 中國 |
執行recursive query
|
1
2
3
4
5
6
7
8
9
10
11
12
|
-- 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 | 中國 | 中國~云南 |
3、繼續執行recursive query，直到結果集和working table為空
4、結束遞歸，將前三個步驟的結果集合并，即得到最終的WITH RECURSIVE的結果集。
嚴格來講，這個過程實現上是一個迭代的過程而非遞歸，不過RECURSIVE這個關鍵詞是SQL標準委員會定立的，所以PostgreSQL也延用了RECURSIVE這一關鍵詞。
總結
以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值，謝謝大家對服務器之家的支持。
原文鏈接：https://juejin.im/post/5cdac101e51d453d022cb666
