背景
處理不確定深度的層級(jí)結(jié)構(gòu),比如組織機(jī)構(gòu),一個(gè)常用的設(shè)計(jì)是在一張表里面保存 ID 和 Parent_ID ,并且通過(guò)自聯(lián)結(jié)的辦法構(gòu)造一顆樹(shù)。這種方式對(duì)寫(xiě)數(shù)據(jù)的過(guò)程很友好,但是查詢過(guò)程就變得相對(duì)復(fù)雜。在不引入MPTT模型的前提下,必須通過(guò)遞歸算法來(lái)查詢某個(gè)節(jié)點(diǎn)和下級(jí)子節(jié)點(diǎn)。
Oracle提供的connect by擴(kuò)展語(yǔ)法,簡(jiǎn)單好用。但是其他的RDBMS就沒(méi)這么人性化了(或者我不知道)。最近在項(xiàng)目中使用PostgreSQL來(lái)查詢樹(shù)形數(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, '中國(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);
connectby函數(shù)
如果安裝了 tablefunc 擴(kuò)展,就可以使用PG版本的connectby函數(shù)。這個(gè)沒(méi)有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 -- 樹(shù)深度,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)
PS:雖然通過(guò)join可以查詢出節(jié)點(diǎn)的code,但是branch部分不能直接轉(zhuǎn)換成對(duì)應(yīng)的code,使用上還是不太方便。
CTE語(yǔ)法
使用CTE語(yǔ)法,通過(guò) with recursive 來(lái)實(shí)現(xiàn)樹(shù)形數(shù)據(jù)的遞歸查詢。這個(gè)方法雖然沒(méi)有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 -- 通過(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ì)算出樹(shù)形的深度 (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)
執(zhí)行過(guò)程說(shuō)明
從上面的例子可以看出,WITH RECURSIVE語(yǔ)句包含了兩個(gè)部分
執(zhí)行步驟如下
以上面的query為例,來(lái)看看具體過(guò)程
執(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 | 中國(guó) | | | 中國(guó)
執(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 | 中國(guó) | 中國(guó)~四川 3 | 云南 | 1 | 中國(guó) | 中國(guó)~云南
3、繼續(xù)執(zhí)行recursive query,直到結(jié)果集和working table為空
4、結(jié)束遞歸,將前三個(gè)步驟的結(jié)果集合并,即得到最終的WITH RECURSIVE的結(jié)果集。
嚴(yán)格來(lái)講,這個(gè)過(guò)程實(shí)現(xiàn)上是一個(gè)迭代的過(guò)程而非遞歸,不過(guò)RECURSIVE這個(gè)關(guān)鍵詞是SQL標(biāo)準(zhǔn)委員會(huì)定立的,所以PostgreSQL也延用了RECURSIVE這一關(guān)鍵詞。
總結(jié)
以上就是這篇文章的全部?jī)?nèi)容了,希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,謝謝大家對(duì)腳本之家的支持。
標(biāo)簽:烏海 晉城 衡陽(yáng) 來(lái)賓 蚌埠 錦州 珠海 株洲
巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《PostgreSQL樹(shù)形結(jié)構(gòu)的遞歸查詢示例》,本文關(guān)鍵詞 PostgreSQL,樹(shù)形,結(jié)構(gòu),的,遞歸,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無(wú)關(guān)。