withrecursive用法詳解

一、基礎知識

1、withrecursive是SQL中遞歸查詢的功能，它允許用戶在一個查詢中多次引用同一查詢並不斷地生成臨時表，實現遞歸查詢的功能。

2、withrecursive的語法格式為：WITH RECURSIVE 臨時表名 AS (查詢語句), SELECT 語句;

3、withrecursive由兩部分組成：第一部分是與文名稱定義了一個臨時表，用於存儲所有生成的結果；第二部分是SELECT語句，用於查詢這個臨時表中的數據。

二、使用withrecursive實現遞歸查詢

1、假設有一張員工表employee，裏面記錄了員工的ID、上級ID和姓名。要求查找某個員工的所有下屬，包括下屬的下屬。

WITH RECURSIVE subordinates (id,name) AS (
    SELECT id,name FROM employee WHERE id = 1 --假設要查找id=1的員工所有下屬
    UNION ALL
    SELECT employee.id,employee.name FROM employee,subordinates WHERE employee.supervisor_id=subordinates.id
)
SELECT * FROM subordinates;

2、上述代碼中，定義了一個名為subordinates的臨時表，SELECT語句查詢了這個臨時表。查詢語句是兩個SELECT語句的組合：

第一個SELECT查詢了id為1的員工，並將其加入臨時表中。

第二個SELECT語句查詢了employee表中所有上級為前一步查詢結果中成員的員工，並將它們加入臨時表中。這個過程由關鍵字UNION ALL連接，表示將兩個查詢結果合併，不去重。

遞歸查詢的過程就這樣進行下去，直到沒有滿足條件的數據為止。

三、使用withrecursive實現遞歸查詢帶層級

1、如果要查找員工表中每個員工的姓名以及他們的上下級關係，可以在subordinates表中增加一個level字段來記錄員工的層級:

WITH RECURSIVE subordinates (id,name,level) AS (
    SELECT id,name,0 FROM employee WHERE id = 1 --假設要查找id=1的員工所有下屬
    UNION ALL
    SELECT employee.id,employee.name,subordinates.level+1 FROM employee,subordinates WHERE employee.supervisor_id=subordinates.id
)
SELECT * FROM subordinates;

2、新增的第三個字段level表示員工所在的層數，它的值是前一行的level加1，這樣創建了一個新的記錄並加入subordinates表中。遞歸過程同樣在UNION ALL之間進行。

四、使用withrecursive實現遞歸查詢找到根節點

1、以上查詢方式只能查找某個員工的下屬，如果要求查找某個員工的上級鏈，可以倒序開始查詢，並將查詢結果反轉。

WITH RECURSIVE superiors(id,name,supervisor_id,level) AS (
    SELECT id,name,supervisor_id,0 FROM employee WHERE id = 9 --假設要查找id=9的員工的上級鏈
UNION ALL
    SELECT employee.id,employee.name,employee.supervisor_id,superiors.level+1 FROM employee,superiors WHERE superiors.supervisor_id=employee.id
)
SELECT id,name FROM superiors ORDER BY level DESC;

2、以上查詢首先選取id=9的員工，然後逐級向上查詢其上級，直到沒有上級為止。

3、這次查詢與上次查詢的區別在於查詢結果按層級逆序排序，從而實現了查詢員工的上級鏈。

五、使用withrecursive實現遞歸查詢自身關聯表樹形結構

1、在自身關聯表中使用withrecursive來查詢樹形結構，例如商品分類表category，每個分類有一個父類別，將其處理成樹形結構。

WITH RECURSIVE category_tree(id,name,parent_id,level,pathinfo) AS (
    SELECT id,name,parent_id, 1, cast(id as varchar(255)) as pathinfo FROM category WHERE parent_id is null 
    UNION ALL 
    SELECT category.id,category.name,category.parent_id, category_tree.level+1, pathinfo || ',' || category.id FROM category,category_tree WHERE category.parent_id = category_tree.id 
)
SELECT id,name,level,pathinfo FROM category_tree ORDER BY pathinfo;

2、以上語句中，定義了名為category_tree的臨時表，包含id、name、parent_id、level、pathinfo 5個字段，SELECT語句查詢category_tree表中的所有數據，並按照pathinfo字段排序。

3、第一個SELECT語句中，選取所有parent_id為null的記錄，並將其放入子表中。每個記錄的初始層級是1，路徑信息為父類別id；

4、第二個SELECT語句連接了category和category_tree表，在每次查詢中將category表中的結果併入category_tree中，並根據父類別id逐層向下查詢，並將級別加1，路徑信息加入父類別路徑末尾。

六、總結

1、withrecursive是SQL中遞歸查詢的主要功能，可以幫助用戶實現各種複雜的查詢和計算任務。

2、使用withrecursive實現遞歸查詢需要考慮查詢的邏輯和關聯關係，同時需要對withrecursive的語法和參數有一定的掌握。