温馨提示×

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

sql
小樊
89
2024-08-28 08:48:39
栏目: 云计算

在 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 包含四个字段:idnameparent_idlevellevel 字段表示部门在层次结构中的层级。

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

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

0