mysql多表树形结构递归实现_MySQL实现递归查找树形结构

MySQL中实现递归查找树形结构，可以使用递归CTE（Common Table Expression）实现。具体步骤如下：

1. 创建测试数据表

假设我们有一个部门表，包含部门ID和上级部门ID两个字段，用于表示部门之间的层级关系。

CREATE TABLE department (
    dept_id INT PRIMARY KEY,
    parent_dept_id INT,
    dept_name VARCHAR(50)
);

INSERT INTO department VALUES (1, NULL, '总部');
INSERT INTO department VALUES (2, 1, '财务部');
INSERT INTO department VALUES (3, 1, '市场部');
INSERT INTO department VALUES (4, 2, '会计组');
INSERT INTO department VALUES (5, 2, '出纳组');
INSERT INTO department VALUES (6, 3, '销售组');

2. 使用递归CTE查询树形结构

使用WITH RECURSIVE关键字来定义递归CTE，语法如下：

WITH RECURSIVE cte_name AS (
    -- non-recursive term
    SELECT ...
    UNION ALL
    -- recursive term
    SELECT ...
)
SELECT ... FROM cte_name;

其中，cte_name是递归CTE的名称，non-recursive term是第一次执行查询时的结果集，recursive term是后续执行查询时的结果集，UNION ALL用于将两次查询的结果集合并起来。

对于部门表，我们可以使用以下SQL语句查询出所有的部门及其子部门：

WITH RECURSIVE dept_tree AS (
    SELECT dept_id, parent_dept_id, dept_name, 0 as level
    FROM department WHERE parent_dept_id IS NULL -- 根节点
    UNION ALL
    SELECT d.dept_id, d.parent_dept_id, d.dept_name, t.level + 1
    FROM department d JOIN dept_tree t ON d.parent_dept_id = t.dept_id -- 子节点
)
SELECT dept_id, parent_dept_id, dept_name, level FROM dept_tree;

解释一下上面的SQL语句：

- 第一次执行查询时，从department表中选出parent_dept_id为NULL的记录，即根节点，level为0；
- 后续执行查询时，将上一次查询结果中的dept_id作为parent_dept_id，在department表中查找其子节点，同时将level加1；
- 重复上述步骤，直到没有子节点为止。

执行上述SQL语句，得到结果如下：

+---------+----------------+-----------+-------+
| dept_id | parent_dept_id | dept_name | level |
+---------+----------------+-----------+-------+
|       1 |           NULL | 总部      |     0 |
|       2 |              1 | 财务部    |     1 |
|       3 |              1 | 市场部    |     1 |
|       4 |              2 | 会计组    |     2 |
|       5 |              2 | 出纳组    |     2 |
|       6 |              3 | 销售组    |     2 |
+---------+----------------+-----------+-------+

可以看到，结果集中包含了所有的部门及其层级关系。