MySQL如何创建递归查询？

当我们需要进行层级结构的数据查询时，MySQL的递归查询功能就尤为重要。下面就让我们来学习一下如何创建MySQL的递归查询。

阅读更多：MySQL 教程

1. 创建数据表

首先，我们需要创建一个数据表作为示例。假设我们有一个部门表Department，其结构如下：

CREATE TABLE `Department` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `parent_id` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

其中parent_id字段指向上级部门的id，如果是顶级部门则值为NULL。

我们给Department表插入几条数据作为示例：

INSERT INTO `Department` (`id`, `name`, `parent_id`) VALUES
(1, '公司', NULL),
(2, '财务部', 1),
(3, '人力资源部', 1),
(4, '行政部', 1),
(5, '会计组', 2),
(6, '审计组', 2),
(7, '招聘组', 3),
(8, '薪酬福利组', 3),
(9, '行政管理组', 4),
(10, '采购组', 4),
(11, '物流组', 4),
(12, '仓储组', 11);

这样，我们就创建了一个包含多个层级结构数据的Department表。

2. 递归查询

为了查询出Department表的层级结构，我们需要使用MySQL的递归查询功能。MySQL的递归查询功能需要使用到WITH RECURSIVE子句，具体语法如下：

WITH RECURSIVE CTE AS (
  -- select initial rows
  SELECT ...
  UNION ALL
  -- select recursive rows
  SELECT ...
)
SELECT * FROM CTE WHERE ...

我们将其应用到Department表的查询中，具体如下：

WITH RECURSIVE CTE AS (
  SELECT id, name, parent_id, 0 as level
  FROM Department
  WHERE parent_id IS NULL
  UNION ALL
  SELECT d.id, d.name, d.parent_id, c.level + 1
  FROM Department d
  JOIN CTE c ON d.parent_id = c.id
)
SELECT * FROM CTE ORDER BY level, id;

以上代码中，我们首先查询出顶层部门，然后通过JOIN操作将顶层部门的下级部门与CTE表关联。通过不断递归联结，我们可以查询出层级结构的数据。对于每个层级的数据，我们增加一个level字段来标示层级深度，方便后续排序。最后我们将查询结果按照层级深度和部门id进行排序输出。

3. 输出结果

执行以上代码后，我们将得到以下结果：

+----+----------------+-----------+-------+
| id | name           | parent_id | level |
+----+----------------+-----------+-------+
|  1 | 公司           |      NULL |     0 |
|  2 | 财务部         |         1 |     1 |
|  3 | 人力资源部     |         1 |     1 |
|  4 | 行政部         |         1 |     1 |
|  5 | 会计组         |         2 |     2 |
|  6 | 审计组         |         2 |     2 |
|  7 | 招聘组         |         3 |     2 |
|  8 | 薪酬福利组     |         3 |     2 |
|  9 | 行政管理组     |         4 |     2 |
| 10 | 采购组         |         4 |     2 |
| 11 | 物流组         |         4 |     2 |
|| 12 | 仓储组         |        11 |     3 |
+----+----------------+-----------+-------+
12 rows in set (0.00 sec)

以上结果展示了Department表的层级结构及其对应的层级深度，可以看出该表为三层结构。