mysql 递归查询父级下的所有子级

By adminmysql360On 2023年6月7日2023年6月5日

有一张组织表，该表有上下层级，使用mysql 递归查询某个组织下的所有子组织，sql要怎么写？

假设这张表名为 organization 的表，其结构如下：

CREATE TABLE organization (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  parent_id INT
);

表里插入了几条数据

INSERT INTO `organization` (`id`, `name`, `parent_id`) VALUES (1, '一级组织', NULL);
INSERT INTO `organization` (`id`, `name`, `parent_id`) VALUES (2, '二级组织1', 1);
INSERT INTO `organization` (`id`, `name`, `parent_id`) VALUES (3, '二级组织2', 1);
INSERT INTO `organization` (`id`, `name`, `parent_id`) VALUES (4, '三级组织1', 2);
INSERT INTO `organization` (`id`, `name`, `parent_id`) VALUES (5, '三级组织2', 2);
INSERT INTO `organization` (`id`, `name`, `parent_id`) VALUES (6, '三级组织3', 3);

在 MySQL 中，可以使用 WITH RECURSIVE 语句来实现递归查询。

其中，id 表示组织的唯一标识符，name 表示组织名称，parent_id 表示父组织的唯一标识符，如果该组织是最高级别组织，则 parent_id 值为 NULL。

现在要查询某个组织下的所有子组织，可以使用以下 SQL 语句：

WITH RECURSIVE cte (id, name, parent_id) AS (
  SELECT id, name, parent_id FROM organization WHERE id = <指定组织的id>
  UNION ALL
  SELECT o.id, o.name, o.parent_id FROM organization o
  INNER JOIN cte ON o.parent_id = cte.id
)
SELECT id, name, parent_id FROM cte;

其中，<指定组织的id> 需要替换成实际的组织标识符。

上述 SQL 语句中的 WITH 子句定义了一个公共表达式（Common Table Expression，CTE）cte，用于存储递归查询中产生的结果集。首先，从 organization 表中选取指定组织，作为递归查询的起点。然后，使用 UNION ALL 操作符将该组织与其子组织连接起来，并将结果递归插入到 cte 表中，直到不再产生新的子组织为止。最后，通过 SELECT 语句从 cte 表中选取所有子组织的信息。

这样，mysql 递归查询就写好了，就能够得到指定组织下的所有子组织的信息。

浏览量: 73