oracle sql递归如何实现 - 问答

在Oracle SQL中，递归查询是通过使用递归公共表达式（Recursive Common Table Expression，简称CTE）来实现的。递归CTE允许我们对具有层次结构或递归关联的数据进行查询。

以下是实现Oracle SQL递归的步骤：

创建递归CTE：首先，我们需要创建一个递归CTE。递归CTE的基本语法如下：

WITH RECURSIVE cte_name (column1, column2, ...) AS (
  -- 基本查询（Base case）
  SELECT column1, column2, ...
  FROM table_name
  WHERE condition
  UNION ALL
  -- 递归查询（Recursive case）
  SELECT column1, column2, ...
  FROM table_name
  WHERE condition
)

其中，cte_name是CTE的名称，column1, column2, ...是CTE中的列名，table_name是包含递归关联数据的表名，condition是用于过滤数据的条件。

基本查询：在递归CTE的基本查询部分，我们从一个或多个表中检索数据。这是递归的终止条件。
递归查询：在递归CTE的递归查询部分，我们再次从相同的表中检索数据，并使用UNION ALL将其与基本查询的结果合并。为了实现递归，我们需要使用一个或多个列来表示数据之间的层次关系。
查询递归CTE：创建递归CTE后，我们可以像查询普通表一样查询它。在查询递归CTE时，Oracle会自动处理递归，直到满足终止条件。

以下是一个简单的示例，说明如何使用递归CTE查询具有层次结构的数据。假设我们有一个包含部门层次结构的公司表（departments），其中每个部门都有一个上级部门ID（parent_id）：

CREATE TABLE departments (
  id NUMBER PRIMARY KEY,
  name VARCHAR2(50),
  parent_id NUMBER
);

INSERT INTO departments (id, name, parent_id)
VALUES (1, 'Head Office', NULL);

INSERT INTO departments (id, name, parent_id)
VALUES (2, 'Sales', 1);

INSERT INTO departments (id, name, parent_id)
VALUES (3, 'Marketing', 1);

INSERT INTO departments (id, name, parent_id)
VALUES (4, 'North', 2);

INSERT INTO departments (id, name, parent_id)
VALUES (5, 'South', 2);

现在，我们可以使用递归CTE查询所有部门及其子部门：

WITH RECURSIVE department_cte (id, name, parent_id, level) AS (
  SELECT id, name, parent_id, 1
  FROM departments
  WHERE parent_id IS NULL
  UNION ALL
  SELECT d.id, d.name, d.parent_id, dept_cte.level + 1
  FROM departments d
  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;

这将返回以下结果：

ID  NAME      PARENT_ID LEVEL
1   Head Office NULL        1
2   Sales     1          2
4   North     2          3
5   South     2          3
3   Marketing 1          2

在这个示例中，我们首先创建了一个名为department_cte的递归CTE，它包含部门ID、名称、上级部门ID和层级。然后，我们在基本查询中检索根部门（parent_id为NULL的部门），并在递归查询中检索其父部门。最后，我们按层级和部门ID对结果进行排序。

0 赞

0 踩