如何通过WITH递归计算层级关系

在SQL中，可以使用WITH RECURSIVE语句递归地计算层级关系。下面是一个示例：

假设我们有一个如下的部门表（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。

我们可以使用WITH RECURSIVE语句计算每个部门的层级：

WITH RECURSIVE department_hierarchy AS (
  SELECT id, name, parent_id, 1 AS level
  FROM department
  WHERE parent_id IS NULL
  UNION ALL
  SELECT d.id, d.name, d.parent_id, dh.level + 1
  FROM department d
  JOIN department_hierarchy dh ON d.parent_id = dh.id
)
SELECT * FROM department_hierarchy;

在这个示例中，我们首先使用WITH子句定义了一个名为department_hierarchy的临时表，用于存储每个部门的层级信息。在这个临时表中，我们选择了id、name、parent_id字段，以及一个名为level的字段，表示部门的层级。

我们使用UNION ALL语句将两个查询结果合并在一起。第一个查询结果包含了顶层部门（即parent_id为NULL的部门），以及它们的层级。第二个查询结果通过将每个部门与其父部门连接起来，计算每个部门的层级。

最后，我们从department_hierarchy临时表中选择所有记录，得到每个部门的层级信息。