sql递归函数如何实现树形查询

在 SQL 中，可以使用递归公共表达式（Recursive Common Table Expressions，简称 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));

这个表包含一个主键字段 id，一个部门名称字段 name，以及一个外键字段 parent_id，该字段指向上级部门的 id。如果部门没有上级部门，则 parent_id 为 NULL。

接下来，我们可以使用递归 CTE 查询该表，以获取部门的层次结构：

WITH RECURSIVE department_cte (id, name, parent_id, level) AS (    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, levelFROM department_cteORDER BY level, id;

这个查询首先使用 WITH RECURSIVE 语句定义了一个名为 department_cte 的递归 CTE。CTE 包含四个字段：id、name、parent_id 和 level。level 字段表示部门在层次结构中的层级。

在 CTE 的定义中，我们首先从根部门（即 parent_id 为 NULL 的部门）开始，将其层级设置为 1。然后，我们使用 UNION ALL 与递归查询部分连接。递归查询部分通过将子部门与父部门（已在 CTE 中）连接在一起来查找子部门。在递归查询部分，我们将子部门的层级设置为父部门的层级加 1。

最后，我们从 CTE 中选择所有记录并按层级和 ID 对其进行排序。这将返回一个包含所有部门及其层次结构的结果集。