如何使用WITH遞歸查詢樹(shù)形結(jié)構(gòu)

小樊
83
2024-09-21 14:58:47

在SQL中，使用遞歸查詢樹(shù)形結(jié)構(gòu)通常涉及到使用公用表表達(dá)式（Common Table Expressions，簡(jiǎn)稱CTE）。公用表表達(dá)式是一個(gè)臨時(shí)的結(jié)果集，可以在查詢中引用。遞歸CTE允許我們對(duì)具有層次結(jié)構(gòu)或遞歸關(guān)聯(lián)的數(shù)據(jù)進(jìn)行查詢。

以下是一個(gè)使用遞歸CTE查詢樹(shù)形結(jié)構(gòu)的示例。假設(shè)我們有一個(gè)如下的部門表（department），表示一個(gè)組織的樹(shù)形結(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)
);

其中，name字段表示部門的名字，parent_id字段表示部門與其父部門之間的關(guān)系。如果部門沒(méi)有父部門，parent_id字段的值為NULL。

現(xiàn)在，我們想要查詢這個(gè)表并顯示每個(gè)部門的層次結(jié)構(gòu)?？梢允褂靡韵逻f歸CTE實(shí)現(xiàn)：

WITH RECURSIVE department_cte (id, name, parent_id, level) AS (
  -- 基本查詢：獲取根部門（parent_id為NULL的部門）
  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;

在這個(gè)查詢中，我們首先定義了一個(gè)名為department_cte的遞歸CTE?；静樵儾糠肢@取所有根部門（parent_id為NULL的部門），并設(shè)置層次級(jí)別為1。接下來(lái)，我們使用遞歸查詢部分，通過(guò)將子部門與父部門關(guān)聯(lián)在一起，逐層添加子部門及其層次級(jí)別。

最后，我們從遞歸CTE中選擇所有記錄，并按層次級(jí)別和部門ID排序。這將顯示每個(gè)部門的完整樹(shù)形結(jié)構(gòu)。

0 踩