sql中with怎么使用 WITH臨時表達式的2種遞歸寫法

遞歸with表達式用于處理層級結構數據，有兩種寫法。一是基本遞歸，包含錨定成員和遞歸成員，適用于單根層級結構；二是多錨點遞歸，包含多個錨定成員，適用于多根層級結構。優化技巧包括限制遞歸深度、使用索引、避免不必要的計算、使用物化視圖。應用場景有網絡拓撲分析、社交網絡分析、權限管理和供應鏈管理。與臨時表相比，with表達式作用域和生命周期更短，性能更好，語法更簡潔。選擇依據是中間結果的使用范圍和存儲需求。

WITH 表達式，說白了，就是sql里的“臨時表”。但它比臨時表更靈活，也更強大。它能讓你在查詢中定義臨時的、命名的結果集，然后在主查詢中引用它們。這讓復雜的SQL變得更易讀、更易維護。而遞歸 WITH，則是解決層級結構數據的利器。

WITH 表達式主要有兩種用法：非遞歸和遞歸。這里重點說說遞歸，因為它更考驗理解。

遞歸 WITH 表達式的兩種寫法

遞歸 WITH 表達式的核心在于：它允許一個 WITH 子句引用它自身。這就像一個函數調用自身一樣，可以用來處理層級數據，比如組織結構、樹形菜單、供應鏈等等。

寫法一：基本遞歸

這種寫法是最常見的，也比較容易理解。它通常包含兩個部分：

1. 錨定成員 (Anchor Member): 這是遞歸的起點，它定義了遞歸的初始結果集。它就像樹的根節點。
2. 遞歸成員 (Recursive Member): 這是遞歸的部分，它引用 WITH 子句自身，并定義了如何從上一次迭代的結果集中生成新的結果集。它就像樹的枝干。

WITH RECURSIVE employee_hierarchy AS (     -- 錨定成員：找到所有頂級員工（沒有上級）     SELECT id, name, manager_id, 1 AS level     FROM employees     WHERE manager_id IS NULL      UNION ALL      -- 遞歸成員：找到所有下級員工     SELECT e.id, e.name, e.manager_id, eh.level + 1 AS level     FROM employees e     JOIN employee_hierarchy eh ON e.manager_id = eh.id ) SELECT * FROM employee_hierarchy;

這段代碼會查詢一個名為 employees 的表，并構建一個員工層級結構。錨定成員找到所有沒有上級（manager_id IS NULL）的員工，作為層級的起點。遞歸成員則通過 JOIN 將每個員工與其上級關聯起來，并遞增層級 level。

寫法二：多錨點遞歸

有時候，你的層級結構可能不是單根的，而是有多個根節點。這時候，就需要使用多錨點遞歸。

WITH RECURSIVE parts_explosion AS (     -- 錨定成員1：找到所有最終產品（沒有零件組成它們）     SELECT part_id, part_name, 1 AS level     FROM parts     WHERE is_final_product = TRUE      UNION ALL      -- 錨定成員2：找到所有原材料（沒有子零件）     SELECT part_id, part_name, 1 AS level     FROM parts     WHERE is_raw_material = TRUE      UNION ALL      -- 遞歸成員：找到所有零件的組成部分     SELECT p.part_id, p.part_name, pe.level + 1 AS level     FROM parts p     JOIN parts_explosion pe ON p.parent_part_id = pe.part_id ) SELECT * FROM parts_explosion;

在這個例子中，我們假設有一個 parts 表，其中包含零件的信息，以及零件之間的組成關系 (parent_part_id)。 這個例子有兩個錨定成員：最終產品和原材料。遞歸成員則通過 parent_part_id 將零件與其組成部分關聯起來。 這種寫法適用于那些有多個起始點的層級結構。

如何優化 WITH 遞歸表達式的性能？

遞歸查詢通常性能較差，特別是當數據量很大或者層級很深時。以下是一些優化技巧：

• 限制遞歸深度: 使用 LIMIT 或者 WHERE 子句來限制遞歸的深度，避免無限遞歸。
• 使用索引: 確保在連接列（比如 manager_id 或者 parent_part_id）上創建了索引。
• 避免不必要的計算: 在遞歸成員中，盡量減少不必要的計算，只計算需要的信息。
• 使用物化視圖: 如果遞歸查詢的結果經常被使用，可以考慮創建一個物化視圖來緩存結果。

WITH 表達式在實際項目中的應用場景有哪些？

除了上面提到的組織結構和零件組成，WITH 表達式還可以用于：

• 網絡拓撲分析: 查找網絡中兩個節點之間的所有路徑。
• 社交網絡分析: 查找用戶之間的共同好友。
• 權限管理: 查找用戶擁有的所有權限，包括直接權限和繼承權限。
• 供應鏈管理: 跟蹤產品的整個生產流程。

WITH 表達式和臨時表有什么區別？

雖然 WITH 表達式和臨時表都可以用來存儲中間結果，但它們之間還是有一些區別：

• 作用域: WITH 表達式的作用域僅限于當前查詢，而臨時表可以在多個查詢中使用。
• 生命周期: WITH 表達式的生命周期僅限于當前查詢的執行時間，而臨時表可以在會話期間存在。
• 性能: WITH 表達式通常比臨時表更快，因為它可以被優化器更好地優化。
• 語法: WITH 表達式的語法更簡潔，更易讀。

選擇使用 WITH 表達式還是臨時表，取決于你的具體需求。如果只需要在單個查詢中使用中間結果，并且希望獲得更好的性能，那么 WITH 表達式是更好的選擇。如果需要在多個查詢中使用中間結果，或者需要長期存儲中間結果，那么臨時表是更好的選擇。