概述

我們平時在處理不確定深度的層級結構，比如組織機構，一個常用的設計是在一張表裡面保存 ID 和 Parent_ID ，並且通過自聯結的辦法構造一顆樹。這種方式對寫數據的過程很友好，但是查詢過程就變得相對複雜。在不引入MPTT模型的前提下，必須通過遞歸算法來查詢某個節點和下級子節點。

Oracle提供的connect by擴展語法，簡單好用。那麼，PG又是怎麼實現呢？

一、CTE

1、with語句

WITH語句通常被稱為通用表表達式（Common Table Expressions）或者CTEs。
WITH語句作為一個輔助語句依附於主語句，WITH語句和主語句都可以是SELECT，INSERT，UPDATE，DELETE中的任何一種語句。

WITH語句最基本的功能是把複雜查詢語句拆分成多個簡單的部分，如下例所示

<code>WITH regional_sales AS (  SELECT region, SUM(amount) AS total_sales  FROM orders  GROUP BY region), top_regions AS (  SELECT region  FROM regional_sales  WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)SELECT  region,  product,  SUM(quantity) AS product_units,  SUM(amount) AS product_salesFROM ordersWHERE region IN (SELECT region FROM top_regions)GROUP BY region, product;/<code>

該例中，定義了兩個WITH輔助語句，regional_sales和top_regions。前者算出每個區域的總銷售量，後者了查出所有銷售量佔所有地區總銷售裡10%以上的區域。主語句通過將這個CTEs及訂單表關聯，算出了頂級區域每件商品的銷售量和銷售額。

2、WITH RECURSIVE
WITH語句還可以通過增加RECURSIVE修飾符來引入它自己，從而實現遞歸

WITH RECURSIVE一般用於處理邏輯上層次化或樹狀結構的數據，典型的使用場景是尋找直接及間接子結點。

嚴格來講，這個過程實現上是一個迭代的過程而非遞歸，不過RECURSIVE這個關鍵詞是SQL標準委員會定立的，所以PostgreSQL也延用了RECURSIVE這一關鍵詞。

二、實驗模擬

1、準備環境

<code>create table tree_data ( id integer, code text, pid integer, sort integer); insert into tree_data values(1, '中國', null, 1);insert into tree_data values(2, '四川', 1, 1);insert into tree_data values(3, '雲南', 1, 2);insert into tree_data values(4, '成都', 2, 1);insert into tree_data values(5, '綿陽', 2, 2);   insert into tree_data values(6, '武侯區', 4, 1);insert into tree_data values(7, '昆明', 3, 1);   /<code>

2、connectby函數
如果安裝了 tablefunc 擴展，就可以使用PG版本的connectby函數。這個沒有Oracle那麼強大，但是可以滿足基本要求。

<code>-- 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);/<code>

PS：雖然通過join可以查詢出節點的code，但是branch部分不能直接轉換成對應的code，使用上還是不太方便。

3、CTE語法

使用CTE語法，通過 with recursive 來實現樹形數據的遞歸查詢。這個方法雖然沒有connectby那麼直接，但是靈活性和顯示效果更好。

<code>with recursive cte as( -- 先查詢root節點  select id, code, pid, '' as pcode, code as branch from 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 tree_data as origin on origin.pid = cte.id)select id,code, pid, pcode, branch,  -- 通過計算分隔符的個數，模擬計算出樹形的深度 (length(branch)-length(replace(branch, '~', ''))) as lvlfrom cte; /<code>

執行過程說明

從上面的例子可以看出，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

覺得有用的朋友多幫忙轉發哦！後面會分享更多devops和DBA方面的內容，感興趣的朋友可以關注下~

閱讀更多 波波說運維 的文章

關鍵字: PostgreSQL 詳解 設計