oracle 表树形结构查询递归查询

川长思鸟来 2024-02-17 09:42 2阅读 0赞

### **简介：** ###

`WITH RECURSIVE` 是一种在关系型数据库中处理递归查询的语法。

### 举例： ###

假设我们有一个树形结构数据表 `tree_table`，

包含节点的 ID、父节点的 ID 和节点名称等字段。

示例表数据：

+----+-----+------+
    | id | pid | name |
    +----+-----+------+
    | 1  | 0   | A    |
    | 2  | 1   | B    |
    | 3  | 1   | C    |
    | 4  | 2   | D    |
    | 5  | 2   | E    |
    +----+-----+------+

示例查询：查询 ID 为 2 的节点及其子节点的信息。

执行以下查询语句：

WITH RECURSIVE cte (id, pid, level, name) AS (
      SELECT id, pid, 0, name
      FROM tree_table
      WHERE id = 2
      UNION ALL
      SELECT t.id, t.pid, cte.level + 1, t.name
      FROM tree_table t
      JOIN cte ON t.pid = cte.id
    )
    
    SELECT id, pid, level, name
    FROM cte
    ORDER BY id;

查询结果：

+----+-----+-------+------+
    | id | pid | level | name |
    +----+-----+-------+------+
    | 2  | 1   | 0     | B    |
    | 4  | 2   | 1     | D    |
    | 5  | 2   | 1     | E    |
    +----+-----+-------+------+

### 总结： ###

### 1. `WITH RECURSIVE` 语句的含义 ###

`WITH RECURSIVE` 语句用于执行递归查询，它可以在查询过程中不断地迭代自身，处理具有递归关系的数据。通过递归查询，我们可以处理树形结构、层次关系等复杂数据结构。

### 2. `WITH RECURSIVE` 语句的执行方式 ###

`WITH RECURSIVE` 语句的执行方式主要包括以下几个步骤：

#### 步骤 1：初始化递归表达式（CTE） ####

在 `WITH RECURSIVE` 语句中，我们首先定义一个递归公共表表达式（CTE），并给它一个名称。在初始化时，我们指定 CTE 的初始结果集，作为递归查询的起点。

#### 步骤 2：执行递归查询 ####

在 `WITH RECURSIVE` 语句中，我们使用 UNION 或 UNION ALL 运算符将初始结果集与递归部分联接起来。递归部分是指在 CTE 中引用 CTE 本身的部分，通过迭代执行递归部分，我们可以一步步扩展结果集。

#### 步骤 3：停止递归查询 ####

递归查询的停止条件通常是满足特定条件或达到预定的递归深度。一旦停止条件满足，递归查询将停止执行，并返回最终的结果集。

#### 步骤 4：返回结果 ####

在 `WITH RECURSIVE` 语句的最后，我们可以通过主查询语句从 CTE 中选择所需的列，并对结果进行排序、过滤或其他操作。

**如果不兼容可以使用以下方法：**

SELECT id, pid, name
    FROM tree_table
    START WITH id = 2
    CONNECT BY PRIOR id = pid
    ORDER BY id;

1.  `START WITH` 子句用于指定起始节点，这里是 `id = 2`，表示从 id 为 2 的节点开始进行递归查询。
2.  `CONNECT BY` 子句是递归查询的关键部分。它定义了递归关系，即 `PRIOR id = pid`，表示递归关系是通过当前行的 id 和父节点的 pid 字段进行连接。`PRIOR` 是一个关键字，用于引用当前行的前一行。
3.  `ORDER BY` 子句用于按照 id 对结果进行排序，以得到按照 id 递增的顺序展示递归节点的信息。

建表及数据：

CREATE TABLE TREE_TABLE (
      id INT,
      pid INT,
      name VARCHAR(10)
    );
    
    INSERT INTO TREE_TABLE (ID,PID,NAME) VALUES
    (1,0,'A'),
    (2,1,'B'),
    (3,1,'C'),
    (4,2,'D'),
    (5,2,'E'),
    (6,3,'F'),
    (7,3,'G');