WITH RECURSIVE hierarchy

`WITH RECURSIVE` 是 SQL 中的一种特性，特别用于编写递归查询（Recursive Query）。当数据存在层级关系，比如树形结构时，这种语法可以帮助你分层次地遍历数据并生成完整的关系图。在 `WITH RECURSIVE` 语句中，你会定义一个或多个基础（基础阶段）和扩展（递归阶段）部分。

例如，在 `hierarchy` 这样的上下文中，基础部分通常包括初始的起点（通常是无父节点的记录），而扩展部分则基于已有的结果进行递归查询，通过 `JOIN` 或者 `UNION ALL` 将当前层的记录与上一层关联起来。这个过程会一直持续，直到满足某个停止条件，如没有更多的父节点或者达到预设的最大层数。

举个例子，如果我们要创建一个查询来找出公司的所有部门及其所有下属，可能的 `WITH RECURSIVE` 语句如下：

WITH RECURSIVE department_hierarchy AS (
  -- 基础阶段：选择根部（无父节点）
  SELECT d.*
  FROM departments d
  WHERE d.parent_id IS NULL

  -- 递归阶段：将每个部门与其子部门合并
  UNION ALL
  SELECT d.*
  FROM departments d
  JOIN department_hierarchy dh ON d.parent_id = dh.id
)
SELECT * FROM department_hierarchy;

这个查询将会返回一个完整的部门层级结构。

相关问题

with recursive

With recursive is a clause used in SQL queries to perform recursive operations on hierarchical data structures. It allows you to repeatedly query a table and its related tables, building up a result set by combining the results of each iteration.

By using the WITH RECURSIVE clause, you can define a recursive query that consists of two parts: the base case and the recursive case. The base case represents the starting point of the recursion, while the recursive case defines how to transform the result set from the previous iteration into the result set for the current iteration.

Here's an example to illustrate how it works:

WITH RECURSIVE RecursiveCTE (id, name, parent_id, level) AS (
  SELECT id, name, parent_id, 0 
  FROM employees 
  WHERE name = 'John'
  
  UNION ALL
  
  SELECT e.id, e.name, e.parent_id, rc.level + 1
  FROM employees e
  INNER JOIN RecursiveCTE rc ON e.parent_id = rc.id
)
SELECT id, name, level
FROM RecursiveCTE;

In this example, we have an "employees" table with columns like "id", "name", and "parent_id". We want to find all the employees that are in the hierarchical chain above "John", including "John" himself. The recursive query starts with the base case where we select "John" and set the initial level to 0. Then, in the recursive case, we join the "employees" table with the RecursiveCTE (the previous iteration of the query) to find employees who have their parent_id equal to the id of the previous iteration. The recursion continues until no more matches are found.

The final result of the query is a result set that includes all the employees in the hierarchical chain above "John", along with their respective levels in the hierarchy.

Note that the specific syntax and capabilities of the WITH RECURSIVE clause may vary depending on the database management system you are using.

解析 WITH recursive dept_hierarchy AS ( SELECT deptid, deptname, fdeptid, 0 AS depth FROM enbogis.sys_dept WHERE deptid = #{area_code} UNION ALL SELECT d.deptid, d.deptname, d.fdeptid, dh.depth + 1 FROM enbogis.sys_dept d JOIN dept_hierarchy dh ON dh.fdeptid = d.deptid ) SELECT deptid, deptname FROM dept_hierarchy ORDER BY depth DESC;

这是一段 SQL 查询语句，使用了 WITH RECURSIVE 关键字进行递归查询。这段代码的作用是查询一个组织机构的层级关系，其中 #{area_code} 是一个变量，表示组织机构的编号。具体地说，这段代码定义了一个名为 dept_hierarchy 的递归查询，首先查询编号为 #{area_code} 的组织机构，然后再递归查询其下属的子组织机构，直到查询到最底层的组织机构。最后，按照层级顺序倒序排序，返回每个组织机构的编号和名称。