快捷導(dǎo)航

SQL語句中實現(xiàn)遞歸查詢操作方法

更新時間：2026年05月22日 09:33:44 作者：rchmin

這篇文章給大家介紹SQL語句中實現(xiàn)遞歸查詢操作方法,本文結(jié)合實例代碼給大家介紹的非常詳細(xì),對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友參考下吧

在SQL中實現(xiàn)遞歸查詢操作，通常有兩種主要的方法：使用遞歸公用表表達(dá)式（Recursive Common Table Expressions，CTEs）和遞歸查詢函數(shù)（例如，在PostgreSQL中使用WITH RECURSIVE，在SQL Server中使用WITH RECURSIVE或CTE，而在Oracle中使用CONNECT BY）。

數(shù)據(jù)表定義：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT  -- 指向上級，CEO的manager_id為NULL（或0，通常用NULL表示無上級）
);

初始化數(shù)據(jù)：

INSERT INTO `employees` (`id`, `name`, `manager_id`) VALUES (101, '馬總', NULL);
INSERT INTO `employees` (`id`, `name`, `manager_id`) VALUES (201, '張工', 101);
INSERT INTO `employees` (`id`, `name`, `manager_id`) VALUES (202, '王工', 101);
INSERT INTO `employees` (`id`, `name`, `manager_id`) VALUES (301, '李工', 201);
INSERT INTO `employees` (`id`, `name`, `manager_id`) VALUES (401, '趙工', 301);
INSERT INTO `employees` (`id`, `name`, `manager_id`) VALUES (402, '劉工', 301);

查詢需求：

請查詢出id=101的人員及其所有的下級信息（包括間接下級），同時標(biāo)記出下屬層級，自身層級設(shè)為0；

查詢語句（MySQL 8.0+）：

要查詢 id = 101 的所有下級（包括直接和間接下級），并標(biāo)記出每個下屬所處的層級（1 表示直接下級，2 表示下級的下級，依此類推），可以使用遞歸公用表表達(dá)式（Recursive CTE）。以下是符合要求的 SQL 語句：

WITH RECURSIVE subordinates AS (
    -- 初始查詢：找到直接下級（manager_id = 101）
    SELECT 
        id,
        name,
        manager_id,
        0 AS level
    FROM employees
    WHERE id = 101
    UNION ALL
    -- 遞歸查詢：找到下一級下屬
    SELECT 
        e.id,
        e.name,
        e.manager_id,
        s.level + 1
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT *
FROM subordinates
ORDER BY level, id;

執(zhí)行結(jié)果：

語法解釋：

WITH RECURSIVE subordinates AS (...) 是 SQL 中用于定義遞歸公用表表達(dá)式（Recursive Common Table Expression）的語法。下面逐部分解釋：

1.WITH關(guān)鍵字

表示定義一個公用表表達(dá)式（CTE，Common Table Expression），類似于一個臨時的命名結(jié)果集，可以在后續(xù)的查詢中引用。
通常用于簡化復(fù)雜查詢，提高可讀性。

2.RECURSIVE關(guān)鍵字

指明該 CTE 是遞歸的，即它可以在自己的定義中引用自身，從而實現(xiàn)迭代或?qū)哟伪闅v。
大部分主流數(shù)據(jù)庫（如 PostgreSQL、MySQL 8.0+、SQL Server、Oracle）都支持該語法，但 MySQL 中必須明確寫出 RECURSIVE，而 SQL Server 中可省略（但需要其他方式表示遞歸）。

3.subordinates– CTE 名稱（自定義）

給這個臨時結(jié)果集取名為 subordinates，后續(xù)的 SELECT 語句就可以像使用普通表一樣使用它。

4. 括號內(nèi)的遞歸定義

遞歸 CTE 通常由兩部分組成，用 UNION ALL 連接：

(1)錨點成員（非遞歸部分）

SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE id = 101

這是遞歸的起點，首先查詢出 id = 101 的員工（即 101 自身）。
這里手動標(biāo)記 level = 0，表示第 0 層級。
錨點成員只執(zhí)行一次，其結(jié)果集作為遞歸的初始數(shù)據(jù)。

(2)遞歸成員（遞歸部分）

SELECT e.id, e.name, e.manager_id, s.level + 1
FROM employees e
INNER JOIN subordinates s ON e.manager_id = s.id

遞歸成員會反復(fù)執(zhí)行，直到不再返回新行。
每次迭代中，它只將上一輪 subordinates 結(jié)果集中的每一行（作為上級 s）與 employees 表連接，找出這些上級的下級員工（e.manager_id = s.id）。
同時，層級別為 s.level + 1，表示比上一級深一層。
新找到的行被加入 subordinates 結(jié)果集，并用于下一次迭代。

(3) 終止條件

當(dāng)某次迭代沒有產(chǎn)生任何新行時，遞歸自動停止。
要求數(shù)據(jù)中沒有循環(huán)引用（例如 A 的上級是 B，B 的上級又是 A），否則可能導(dǎo)致無限遞歸。大多數(shù)數(shù)據(jù)庫默認(rèn)設(shè)置了遞歸深度限制（如 MySQL 的 cte_max_recursion_depth）。