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, level
FROM department_cte
ORDER 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 对其进行排序。这将返回一个包含所有部门及其层次结构的结果集。

0 赞

0 踩