【Sql遞歸查詢】Mysql、Oracle、SQL Server、PostgreSQL 實現遞歸查詢的區別與案例（詳解）

在這里插入圖片描述

文章目錄

- Mysql 5.7 遞歸查詢
- Mysql 8 實現遞歸查詢
- Oracle遞歸示例
- SQL Server 遞歸查詢示例
- PostgreSQL 遞歸查詢示例

更多相關內容可查看

Mysql 5.7 遞歸查詢

MySQL 5.7 本身不直接支持標準 SQL 中的遞歸查詢語法（如 WITH RECURSIVE 這種常見的遞歸查詢方式），但可以通過使用存儲過程、臨時表或自連接等方式來實現遞歸查詢的效果。

使用自連接實現遞歸查詢

通過自連接的方式模擬遞歸查詢，適合處理簡單的遞歸結構。假設我們有一個表示部門層級關系的表 departments，結構如下：

CREATE TABLE departments (id INT PRIMARY KEY,name VARCHAR(50),parent_id INT
);

向表中插入一些示例數據：

INSERT INTO departments (id, name, parent_id) VALUES
(1, '總公司', NULL),
(2, '研發部', 1),
(3, '市場部', 1),
(4, '研發一組', 2),
(5, '研發二組', 2);

使用自連接查詢所有部門及其子部門：

在這里插入圖片描述

SELECTt1.id AS root_id,t1.name AS root_name,t2.id AS child_id,t2.name AS child_name
FROMdepartments t1
JOINdepartments t2
ONt1.id = t2.parent_id
UNION
SELECTid AS root_id,name AS root_name,id AS child_id,name AS child_name
FROMdepartments
WHEREparent_id IS NULL;

在這個查詢中，通過 JOIN 語句將父部門和子部門關聯起來，然后使用 UNION 操作符將頂級部門（parent_id 為 NULL）也包含在結果中。

使用存儲過程實現遞歸查詢

DELIMITER //-- 創建一個名為 recursive_departments_func 的函數，該函數接收兩個整數參數 p_parent_id 和 p_level，并返回一個整數
CREATE FUNCTION recursive_departments_func(p_parent_id INT, p_level INT) RETURNS INT
DETERMINISTIC
BEGIN-- 聲明一個整數變量 done，用于標記游標是否已經完成遍歷，初始值為 FALSEDECLARE done INT DEFAULT FALSE;-- 聲明一個整數變量 v_id，用于存儲從游標中獲取的部門 idDECLARE v_id INT;-- 聲明一個字符串變量 v_name，用于存儲從游標中獲取的部門名稱DECLARE v_name VARCHAR(50);-- 聲明一個游標 cur，用于查詢 departments 表中 parent_id 等于 p_parent_id 的記錄DECLARE cur CURSOR FORSELECT id, name FROM departments WHERE parent_id = p_parent_id;-- 聲明一個繼續處理程序，當游標沒有更多數據時，將 done 置為 TRUEDECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;-- 創建一個臨時表 temp_departments，用于存儲遞歸調用的結果-- 僅在該表不存在時創建，包含三個列：id、name 和 levelCREATE TEMPORARY TABLE IF NOT EXISTS temp_departments (id INT,name VARCHAR(50),level INT);-- 打開游標 cur，以便開始讀取數據OPEN cur;-- 定義一個名為 read_loop 的循環標簽read_loop: LOOP-- 從游標 cur 中獲取數據并存儲到 v_id 和 v_name 中FETCH cur INTO v_id, v_name;-- 檢查 done 變量是否為 TRUE，如果是則離開循環IF done THENLEAVE read_loop;END IF;-- 將當前部門的信息插入到臨時表 temp_departments 中INSERT INTO temp_departments (id, name, level) VALUES (v_id, v_name, p_level);-- 遞歸調用 recursive_departments_func 函數，將當前部門的 id 作為新的父部門 id，層級加 1SET @result = recursive_departments_func(v_id, p_level + 1);END LOOP;-- 關閉游標 curCLOSE cur;-- 函數最終返回 1RETURN 1;
END //DELIMITER ;

Mysql 8 實現遞歸查詢

在 MySQL 8 中，可以使用 WITH RECURSIVE 子句來實現遞歸查詢。

創建示例數據

假設我們有一個表示員工層級關系的表 employees，其中包含員工編號、姓名、上級員工編號：

-- 創建表
CREATE TABLE employees (employee_id INT PRIMARY KEY,name VARCHAR(50),manager_id INT,FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);-- 插入數據
INSERT INTO employees (employee_id, name, manager_id) VALUES
(1, 'CEO', NULL),
(2, 'CTO', 1),
(3, 'CFO', 1),
(4, 'Lead Developer', 2),
(5, 'Developer 1', 4),
(6, 'Developer 2', 4),
(7, 'Accountant', 3);

遞歸查詢所有員工及其下屬

使用 WITH RECURSIVE 子句進行遞歸查詢，查找某個員工及其所有下屬。以下是查詢 CEO 及其所有下屬的示例：

WITH RECURSIVE employee_hierarchy AS (-- 初始查詢，找到CEOSELECT employee_id, name, manager_idFROM employeesWHERE name = 'CEO'UNION ALL-- 遞歸部分，找到下屬員工SELECT e.employee_id, e.name, e.manager_idFROM employees eINNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

2.1. CTE（公共表表達式）定義：
- WITH RECURSIVE employee_hierarchy AS (...) 定義了一個名為 employee_hierarchy 的遞歸 CTE。
- 初始查詢部分：
```
SELECT employee_id, name, manager_id
FROM employees
WHERE name = 'CEO'
```
  這部分找到 CEO 的記錄，作為遞歸的起點。
- UNION ALL 用于將初始查詢結果和遞歸查詢結果合并。
- 遞歸部分：
```
SELECT e.employee_id, e.name, e.manager_id
FROM employees e
INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
```
  這部分通過連接 employees 表和遞歸生成的 employee_hierarchy 表，找到每個員工的下屬。

反向遞歸查詢（查找某個員工的所有上級）

查找某個員工（例如 Developer 1）的所有上級：

WITH RECURSIVE manager_hierarchy AS (-- 初始查詢，找到Developer 1SELECT employee_id, name, manager_idFROM employeesWHERE name = 'Developer 1'UNION ALL-- 遞歸部分，找到上級員工SELECT e.employee_id, e.name, e.manager_idFROM employees eINNER JOIN manager_hierarchy mh ON e.employee_id = mh.manager_id
)
SELECT * FROM manager_hierarchy;

這個查詢同樣使用 WITH RECURSIVE，但遞歸方向是從指定員工向上查找其所有上級。

組織遞歸查詢示例

-- 假設我們有一個 organizations 表存儲組織信息
CREATE TABLE organizations (id INT PRIMARY KEY AUTO_INCREMENT,parent_id INT,name VARCHAR(255),level INT
);-- 假設我們有一個 employees 表存儲員工信息
CREATE TABLE employees (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(255),organization_id INT
);-- 插入一些示例數據到 organizations 表
INSERT INTO organizations (parent_id, name, level) VALUES(NULL, '總公司', 1),(1, '分公司 A', 2),(1, '分公司 B', 2),(2, '部門 A1', 3),(2, '部門 A2', 3),(3, '部門 B1', 3),(3, '部門 B2', 3),(4, '小組 A1-1', 4),(4, '小組 A1-2', 4);-- 插入一些示例數據到 employees 表
INSERT INTO employees (name, organization_id) VALUES('員工 1', 1),('員工 2', 2),('員工 3', 2),('員工 4', 3),('員工 5', 4),('員工 6', 4),('員工 7', 4),('員工 8', 5),('員工 9', 6),('員工 10', 7),('員工 11', 8);-- 使用 WITH RECURSIVE 進行遞歸查詢
WITH RECURSIVE organization_hierarchy AS (-- 非遞歸部分：選擇根組織作為起始點SELECT id, parent_id, name, level, 0 AS depthFROM organizationsWHERE id = 1UNION ALL-- 遞歸部分：選擇子組織，深度加 1SELECT o.id, o.parent_id, o.name, o.level, oh.depth + 1FROM organizations oJOIN organization_hierarchy oh ON o.parent_id = oh.id
)
-- 從遞歸結果中選擇信息并統計員工數量
SELECT oh.id, oh.parent_id, oh.name, oh.level, oh.depth, COUNT(e.id) AS employee_count
FROM organization_hierarchy oh
LEFT JOIN employees e ON oh.id = e.organization_id
GROUP BY oh.id, oh.parent_id, oh.name, oh.level, oh.depth
ORDER BY oh.depth, oh.id;

Oracle遞歸示例

支持版本：Oracle 9i 開始引入遞歸查詢的功能，通過 CONNECT BY 子句實現。從 Oracle 11g 開始支持使用 WITH RECURSIVE 語法（CTE 遞歸查詢）。
示例1：假設有一個表示部門層級關系的表 departments，結構為 (department_id, department_name, parent_department_id)。

-- 使用 CONNECT BY 子句
SELECT department_id, department_name, parent_department_id
FROM departments
START WITH parent_department_id IS NULL
CONNECT BY PRIOR department_id = parent_department_id;-- 使用 WITH RECURSIVE 語法
WITH RECURSIVE department_hierarchy AS (SELECT department_id, department_name, parent_department_idFROM departmentsWHERE parent_department_id IS NULLUNION ALLSELECT d.department_id, d.department_name, d.parent_department_idFROM departments dINNER JOIN department_hierarchy dh ON d.parent_department_id = dh.department_id
)
SELECT * FROM department_hierarchy;

示例2：使用 CONNECT BY 和 START WITH 子句進行遞歸查詢，以查詢 id 為 1 的組織（總公司）及其所有子組織。

CREATE TABLE organizations (id        NUMBER PRIMARY KEY,parent_id NUMBER,name      VARCHAR2(100)
);INSERT INTO organizations (id, parent_id, name) VALUES (1, NULL, '總公司');
INSERT INTO organizations (id, parent_id, name) VALUES (2, 1, '分公司 A');
INSERT INTO organizations (id, parent_id, name) VALUES (3, 1, '分公司 B');
INSERT INTO organizations (id, parent_id, name) VALUES (4, 2, '部門 A1');
INSERT INTO organizations (id, parent_id, name) VALUES (5, 2, '部門 A2');
INSERT INTO organizations (id, parent_id, name) VALUES (6, 3, '部門 B1');
INSERT INTO organizations (id, parent_id, name) VALUES (7, 3, '部門 B2');
INSERT INTO organizations (id, parent_id, name) VALUES (8, 4, '小組 A1-1');
INSERT INTO organizations (id, parent_id, name) VALUES (9, 4, '小組 A1-2');SELECT o.id, o.parent_id, o.name, LEVEL
FROM organizations o
START WITH o.id = 1
CONNECT BY PRIOR o.id = o.parent_id;

示例3：使用遞歸查詢和 JOIN 操作計算每個組織及其子組織的員工總數。

CREATE TABLE employees (id           NUMBER PRIMARY KEY,name         VARCHAR2(100),organization_id NUMBER
);INSERT INTO employees (id, name, organization_id) VALUES (1, '員工 1', 2);
INSERT INTO employees (id, name, organization_id) VALUES (2, '員工 2', 2);
INSERT INTO employees (id, name, organization_id) VALUES (3, '員工 3', 3);
INSERT INTO employees (id, name, organization_id) VALUES (4, '員工 4', 4);
INSERT INTO employees (id, name, organization_id) VALUES (5, '員工 5', 4);
INSERT INTO employees (id, name, organization_id) VALUES (6, '員工 6', 5);
INSERT INTO employees (id, name, organization_id) VALUES (7, '員工 7', 6);
INSERT INTO employees (id, name, organization_id) VALUES (8, '員工 8', 7);
INSERT INTO employees (id, name, organization_id) VALUES (9, '員工 9', 8);WITH org_hierarchy AS (SELECT o.id, o.parent_id, o.name, LEVEL AS org_levelFROM organizations oSTART WITH o.id = 1CONNECT BY PRIOR o.id = o.parent_id
)
SELECT oh.id, oh.parent_id, oh.name, oh.org_level, COUNT(e.id) AS employee_count
FROM org_hierarchy oh
LEFT JOIN employees e ON oh.id = e.organization_id
GROUP BY oh.id, oh.parent_id, oh.name, oh.org_level
ORDER BY oh.org_level, oh.id;

示例4：假設 organizations 表有一個 budget 列表示組織的預算，并且預算可以從父組織分配給子組織。我們可以使用遞歸查詢計算每個組織及其子組織的最終預算

ALTER TABLE organizations ADD (budget NUMBER);UPDATE organizations SET budget = 100000 WHERE id = 1;
UPDATE organizations SET budget = 0 WHERE id IN (2, 3);
UPDATE organizations SET budget = 0 WHERE id IN (4, 5, 6, 7);
UPDATE organizations SET budget = 0 WHERE id IN (8, 9);WITH budget_allocation AS (SELECT o.id, o.parent_id, o.name, o.budget AS original_budget,o.budget AS allocated_budget, LEVEL AS org_levelFROM organizations oSTART WITH o.id = 1CONNECT BY PRIOR o.id = o.parent_id
)
SELECT ba.id, ba.parent_id, ba.name, ba.original_budget,CASEWHEN ba.original_budget = 0 THENNVL((LAG(ba.allocated_budget) OVER (ORDER BY ba.org_level DESC) / COUNT(*) OVER (PARTITION BY ba.parent_id)), 0)ELSE ba.allocated_budgetEND AS final_budget,ba.org_level
FROM budget_allocation ba;

SQL Server 遞歸查詢示例

支持版本：SQL Server 2005 開始支持 WITH 子句，包括遞歸 CTE（Common Table Expressions）。
示例：假設有一個員工表 Employees，結構為 (EmployeeID, Name, ManagerID)。

WITH RECURSIVE EmployeeHierarchy AS (SELECT EmployeeID, Name, ManagerIDFROM EmployeesWHERE ManagerID IS NULLUNION ALLSELECT e.EmployeeID, e.Name, e.ManagerIDFROM Employees eINNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

PostgreSQL 遞歸查詢示例

支持版本：PostgreSQL 8.4 開始支持遞歸 CTE（WITH RECURSIVE）。
示例：假設有一個表示菜單層級關系的表 menus，結構為 (menu_id, menu_name, parent_menu_id)。

WITH RECURSIVE menu_hierarchy AS (SELECT menu_id, menu_name, parent_menu_idFROM menusWHERE parent_menu_id IS NULLUNION ALLSELECT m.menu_id, m.menu_name, m.parent_menu_idFROM menus mINNER JOIN menu_hierarchy mh ON m.parent_menu_id = mh.menu_id
)
SELECT * FROM menu_hierarchy;