MySQL 如何从自引用表中获取层级数据

MySQL是一款常用的开源关系数据库管理系统，支持多种操作系统，如Linux、Unix和Windows。本文将介绍如何从自引用表中获取层级数据。即，自我参考表是一个表格，其中一些记录通过指向同一表中其他记录的外键来形成分层结构。这样的表通常用于表示组织架构、产品分类等层级结构。

阅读更多：MySQL 教程

问题描述

假设我们有一个名为Org的表，其中包含以下列：
– id：组织机构id
– name：组织机构名称
– parent_id：父级组织机构id

其中，parent_id引用了同一表中另一个记录的id，表示它的父级组织机构。我们的任务是获取给定组织机构的所有子组织机构，以及它们的子孙组织机构，包括它们的名称和id。

我们将使用以下示例Org表格：

CREATE TABLE `Org` (
    `id` INT NOT NULL, 
    `name` VARCHAR(50) NOT NULL, 
    `parent_id` INT NULL, 
    PRIMARY KEY (`id`), 
    FOREIGN KEY (`parent_id`) REFERENCES `Org` (`id`)
);

INSERT INTO Org(id, name, parent_id) VALUES(1, '组织机构1', NULL); 
INSERT INTO Org(id, name, parent_id) VALUES(2, '组织机构2', 1); 
INSERT INTO Org(id, name, parent_id) VALUES(3, '组织机构3', 1); 
INSERT INTO Org(id, name, parent_id) VALUES(4, '组织机构4', 2); 
INSERT INTO Org(id, name, parent_id) VALUES(5, '组织机构5', 2); 
INSERT INTO Org(id, name, parent_id) VALUES(6, '组织机构6', 3); 
INSERT INTO Org(id, name, parent_id) VALUES(7, '组织机构7', 3); 
INSERT INTO Org(id, name, parent_id) VALUES(8, '组织机构8', 4); 
INSERT INTO Org(id, name, parent_id) VALUES(9, '组织机构9', 4); 
INSERT INTO Org(id, name, parent_id) VALUES(10, '组织机构10', 5); 
INSERT INTO Org(id, name, parent_id) VALUES(11, '组织机构11', 5); 
INSERT INTO Org(id, name, parent_id) VALUES(12, '组织机构12', 6); 
INSERT INTO Org(id, name, parent_id) VALUES(13, '组织机构13', 6); 
INSERT INTO Org(id, name, parent_id) VALUES(14, '组织机构14', 7); 
INSERT INTO Org(id, name, parent_id) VALUES(15, '组织机构15', 7);

解决方案

在进行本教程的其余部分之前，请确保您已经安装并启动来自 http://dev.mysql.com/downloads/ 的MySQL。我们将使用MySQL的递归查询功能来解决这个问题。

1. 查询所有子组织机构

为了找到所有子组织机构，我们可以使用与父组织机构关联的递归查询。

WITH RECURSIVE CTE AS 
(
SELECT id, name, parent_id 
FROM Org 
WHERE id = 1 

UNION ALL 

SELECT c.id, c.name, c.parent_id 
FROM Org AS c JOIN CTE 
ON c.parent_id = CTE.id 
) 
SELECT * 
FROM CTE;

查询的结果为：

+----+-------------+-----------+
| id | name        | parent_id |
+----+-------------+-----------+
|  1 | 组织机构1     |      NULL |
|  2 | 组织机构2     |         1 |
|  4 | 组织机构4     |         2 |
|  8 | 组织机构8     |         4 |
| 9 | 组织机构9     |         4 |
|  5 | 组织机构5     |         2 |
| 10 | 组织机构10   |         5 |
| 11 | 组织机构11   |         5 |
|  3 | 组织机构3     |         1 |
|  6 | 组织机构6     |         3 |
| 12 | 组织机构12   |         6 |
| 13 | 组织机构13   |         6 |
|  7 | 组织机构7     |         3 |
| 14 | 组织机构14   |         7 |
| 15 | 组织机构15   |         7 |
+----+-------------+-----------+

从结果中可以看到所有组织机构都有一个相同的根节点（id=1）。

2. 查询特定组织机构的所有子组织机构

要查询特定组织机构的所有子组织机构，需要在查询中使用该组织机构的id。例如，如果我们要查询“组织机构5”的所有子组织机构，则可以将上面的查询修改为：

WITH RECURSIVE CTE AS 
(
SELECT id, name, parent_id 
FROM Org 
WHERE id = 5 

UNION ALL 

SELECT c.id, c.name, c.parent_id 
FROM Org AS c JOIN CTE 
ON c.parent_id = CTE.id 
) 
SELECT * 
FROM CTE;

查询的结果为：

+----+------------+-----------+
| id | name       | parent_id |
+----+------------+-----------+
|  5 | 组织机构5    |         2 |
| 10 | 组织机构10  |         5 |
| 11 | 组织机构11  |         5 |
+----+------------+-----------+

从结果中可以看到，“组织机构5”有两个子组织机构：“组织机构10”和“组织机构11”。

3. 查询特定组织机构的所有子孙组织机构

要查询特定组织机构的所有子孙组织机构，需要在查询中使用它的id，并稍微修改查询中的条件。例如，如果我们要查询“组织机构2”的所有子孙组织机构，则可以将上面的查询修改为：

WITH RECURSIVE CTE AS 
(
SELECT id, name, parent_id 
FROM Org 
WHERE id = 2 

UNION ALL 

SELECT c.id, c.name, c.parent_id 
FROM Org AS c JOIN CTE 
ON c.parent_id = CTE.id 
) 
SELECT * 
FROM CTE 
WHERE id != 2;

查询的结果为：

+----+------------+-----------+
| id | name       | parent_id |
+----+------------+-----------+
|  4 | 组织机构4    |         2 |
|  8 | 组织机构8    |         4 |
|  9 | 组织机构9    |         4 |
|  5 | 组织机构5    |         2 |
| 10 | 组织机构10  |         5 |
| 11 | 组织机构11  |         5 |
+----+------------+-----------+

从结果中可以看到，“组织机构2”有四个子孙组织机构：“组织机构4”、“组织机构8”、“组织机构9”和“组织机构5”，以及它们的子孙组织机构。