如何使用WITH递归查询树形结构 - 问答

在SQL中，使用递归查询树形结构通常涉及到使用公用表表达式（Common Table Expressions，简称CTE）。公用表表达式是一个临时的结果集，可以在查询中引用。递归CTE允许我们对具有层次结构或递归关联的数据进行查询。

以下是一个使用递归CTE查询树形结构的示例。假设我们有一个如下的部门表（department），表示一个组织的树形结构：

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字段表示部门与其父部门之间的关系。如果部门没有父部门，parent_id字段的值为NULL。

现在，我们想要查询这个表并显示每个部门的层次结构。可以使用以下递归CTE实现：

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;

在这个查询中，我们首先定义了一个名为department_cte的递归CTE。基本查询部分获取所有根部门（parent_id为NULL的部门），并设置层次级别为1。接下来，我们使用递归查询部分，通过将子部门与父部门关联在一起，逐层添加子部门及其层次级别。

最后，我们从递归CTE中选择所有记录，并按层次级别和部门ID排序。这将显示每个部门的完整树形结构。

0 赞

0 踩