前言

遞歸查詢是 SQL 中非常強大的一種功能，它用于處理具有層次結(jié)構(gòu)或樹形結(jié)構(gòu)的數(shù)據(jù)。在 Oracle 中，遞歸查詢主要通過 START WITH 和 CONNECT BY 子句實現(xiàn)，這是 Oracle 的傳統(tǒng)方法。從 Oracle 11g Release 2 開始，它也支持 ANSI SQL 標(biāo)準(zhǔn)的 WITH 子句（即公共表表達(dá)式 CTE）形式的遞歸查詢。

我將重點介紹最常用、也是 Oracle 特色的 START WITH ... CONNECT BY 語法，并簡要對比標(biāo)準(zhǔn)的遞歸 CTE 方法。

一、核心概念與語法 (START WITH … CONNECT BY)

這種語法專門為處理層次查詢而設(shè)計，非常直觀和高效。

基本語法結(jié)構(gòu)：

SELECT [LEVEL], column1, column2, ...
FROM table_name
[WHERE ...]
START WITH condition -- 指定層次結(jié)構(gòu)的根節(jié)點（起點）
CONNECT BY [PRIOR] condition -- 定義父節(jié)點和子節(jié)點之間的關(guān)系
[ORDER SIBLINGS BY column_name]; -- 按兄弟節(jié)點排序

關(guān)鍵元素解釋：

1. LEVEL 偽列：

   • 這是一個系統(tǒng)自動生成的偽列，它表示當(dāng)前行在樹形結(jié)構(gòu)中的層級。
   • 根節(jié)點的 LEVEL 為 1，根節(jié)點的直接子節(jié)點為 2，以此類推。

2. START WITH 子句：

   • 用于指定遞歸開始的根節(jié)點（一行或幾行）。
   • 例如：START WITH employee_id = 100 表示從員工 ID 為 100 的 CEO 開始構(gòu)建樹。

3. CONNECT BY 子句：

   • 這是遞歸查詢的核心，它定義了父行和子行之間的關(guān)系。
   • PRIOR 運算符：至關(guān)重要。它引用的是父行的列。
     • CONNECT BY PRIOR child_id = parent_id：表示上一行的 child_id 等于當(dāng)前行的 parent_id。這通常用于從父節(jié)點向下遍歷到子節(jié)點（自上而下）。
     • CONNECT BY child_id = PRIOR parent_id：表示當(dāng)前行的 child_id 等于上一行的 parent_id。這可以用于從子節(jié)點向上遍歷到根節(jié)點（自下而上）。

4. ORDER SIBLINGS BY 子句：

   • 在保持層次結(jié)構(gòu)完整性的前提下，對同一父節(jié)點下的兄弟節(jié)點進(jìn)行排序。
   • 比直接在最后用 ORDER BY 更合理，因為它不會打亂樹的顯示順序。

二、經(jīng)典示例：員工組織架構(gòu)圖

假設(shè)我們有一個 employees 表，結(jié)構(gòu)如下：

需求： 查詢所有員工，并顯示他們的匯報層級關(guān)系。

查詢語句（自上而下）：

SELECT
    LEVEL,
    LPAD(' ', (LEVEL-1)*4, ' ') || NAME AS Indented_Name, -- 用縮進(jìn)直觀顯示層級
    EMPLOYEE_ID,
    NAME,
    MANAGER_ID,
    JOB_TITLE
FROM employees
START WITH MANAGER_ID IS NULL -- 從最大的老板開始（沒有經(jīng)理的人）
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID -- 上一行的員工ID = 當(dāng)前行的經(jīng)理ID
ORDER SIBLINGS BY NAME; -- 同一經(jīng)理下的員工按名字排序

查詢結(jié)果可能如下：

從這個結(jié)果可以清晰地看出 King 是根節(jié)點，De Haan 和 Kochhar 向他匯報，Hunold 向 De Haan 匯報，Ernst 向 Hunold 匯報。

三、其他有用的運算符和函數(shù)

1. CONNECT_BY_ROOT：

   • 用于獲取當(dāng)前行所在樹的根節(jié)點的某列值。
   • SELECT CONNECT_BY_ROOT NAME AS Top_Manager, NAME ... 會為 Ernst 顯示 Top_Manager 是 King。

2. SYS_CONNECT_BY_PATH：

   • 顯示從根節(jié)點到當(dāng)前節(jié)點的完整路徑。
   • SELECT SYS_CONNECT_BY_PATH(NAME, ' -> ') AS Path ... 對于 Ernst，會顯示 -> King -> De Haan -> Hunold -> Ernst。

3. CONNECT_BY_ISLEAF：

   • 判斷當(dāng)前行是否是葉子節(jié)點（即沒有子節(jié)點）。是葉子節(jié)點則返回 1，否則返回 0。

四、ANSI SQL 標(biāo)準(zhǔn)方法：遞歸公用表表達(dá)式 (CTE)

Oracle 也支持使用 WITH 子句進(jìn)行遞歸查詢，語法更符合其他數(shù)據(jù)庫（如 PostgreSQL, SQL Server）的標(biāo)準(zhǔn)。

語法結(jié)構(gòu)：

WITH cte_name (column_list) AS (
    -- 錨定成員 (Anchor Member)：定義根節(jié)點
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition -- 類似于 START WITH

    UNION ALL

    -- 遞歸成員 (Recursive Member)：引用CTE自身，進(jìn)行遞歸join
    SELECT t.column1, t.column2, ...
    FROM table_name t
    JOIN cte_name c ON t.parent_id = c.child_id -- 類似于 CONNECT BY
)
-- 主查詢
SELECT * FROM cte_name;

用遞歸 CTE 實現(xiàn)上面的例子：

WITH Employee_Tree (LEVEL, EMPLOYEE_ID, NAME, MANAGER_ID, JOB_TITLE) AS (
    -- 錨定成員：找到根節(jié)點
    SELECT
        1 AS LEVEL,
        EMPLOYEE_ID,
        NAME,
        MANAGER_ID,
        JOB_TITLE
    FROM employees
    WHERE MANAGER_ID IS NULL

    UNION ALL

    -- 遞歸成員：連接員工表和CTE自身
    SELECT
        p.LEVEL + 1, -- 層級增加
        e.EMPLOYEE_ID,
        e.NAME,
        e.MANAGER_ID,
        e.JOB_TITLE
    FROM employees e
    INNER JOIN Employee_Tree p ON e.MANAGER_ID = p.EMPLOYEE_ID
)
SELECT * FROM Employee_Tree
ORDER BY LEVEL, NAME;

五、兩種方法的對比

總結(jié)

• 對于 Oracle 環(huán)境下的開發(fā)，START WITH ... CONNECT BY 是處理遞歸查詢的首選，因為它語法簡潔、功能專一且性能優(yōu)異。
• 如果你需要編寫跨數(shù)據(jù)庫兼容的 SQL，或者希望遞歸邏輯更符合通用的編程思維（先錨定再遞歸），那么應(yīng)該使用遞歸 CTE (WITH 子句)。

無論是哪種方法，遞歸查詢都是操作樹形結(jié)構(gòu)數(shù)據(jù)（如組織架構(gòu)、菜單、分類目錄、BOM物料清單）的利器。

到此這篇關(guān)于Oracle遞歸查詢的文章就介紹到這了,更多相關(guān)Oracle遞歸查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論