溫馨提示×

sql遞歸函數(shù)如何實現(xiàn)樹形查詢

sql
小樊
86
2024-08-28 08:48:39
欄目: 云計算

在 SQL 中，可以使用遞歸公共表達式（Recursive Common Table Expressions，簡稱 CTE）來實現(xiàn)樹形查詢。遞歸 CTE 允許我們對具有層次結(jié)構(gòu)或遞歸關(guān)聯(lián)的數(shù)據(jù)進行查詢。下面是一個實現(xiàn)樹形查詢的例子：

假設(shè)我們有一個部門表（department），其結(jié)構(gòu)如下：

CREATE TABLE department (
    id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES department(id)
);

這個表包含一個主鍵字段 id，一個部門名稱字段 name，以及一個外鍵字段 parent_id，該字段指向上級部門的 id。如果部門沒有上級部門，則 parent_id 為 NULL。

接下來，我們可以使用遞歸 CTE 查詢該表，以獲取部門的層次結(jié)構(gòu)：

WITH RECURSIVE department_cte (id, name, parent_id, level) AS (
    SELECT id, name, parent_id, 1
    FROM department
    WHERE parent_id IS NULL
    UNION ALL
    SELECT d.id, d.name, d.parent_id, dept_cte.level + 1
    FROM department d
    INNER JOIN department_cte dept_cte ON d.parent_id = dept_cte.id
)
SELECT id, name, parent_id, level
FROM department_cte
ORDER BY level, id;

這個查詢首先使用 WITH RECURSIVE 語句定義了一個名為 department_cte 的遞歸 CTE。CTE 包含四個字段：id、name、parent_id 和 level。level 字段表示部門在層次結(jié)構(gòu)中的層級。

在 CTE 的定義中，我們首先從根部門（即 parent_id 為 NULL 的部門）開始，將其層級設(shè)置為 1。然后，我們使用 UNION ALL 與遞歸查詢部分連接。遞歸查詢部分通過將子部門與父部門（已在 CTE 中）連接在一起來查找子部門。在遞歸查詢部分，我們將子部門的層級設(shè)置為父部門的層級加 1。

最后，我們從 CTE 中選擇所有記錄并按層級和 ID 對其進行排序。這將返回一個包含所有部門及其層次結(jié)構(gòu)的結(jié)果集。

0 踩