一句SQL实现MySQL递归查询

1. 简介

在数据库中，递归查询是指在查询过程中通过引用自身的结果来进行进一步的查询。MySQL作为一种常用的关系型数据库管理系统，同样支持递归查询。本文将重点介绍如何使用一句SQL语句实现MySQL的递归查询功能。

2. 递归查询的应用场景

递归查询适用于一些具有层级结构的数据，比如组织架构、树形结构等。常见的应用场景包括：

查询员工及其下属员工的信息；
查询商品的分类及其所有子分类的信息；
查询节点及其所有子节点的关系等。

3. MySQL递归查询的实现方法

MySQL中递归查询可以通过使用WITH RECURSIVE语句来完成。该语句可以将查询结果作为子查询，并将其结果作为输入继续进行查询，直到达到指定的结束条件。

4. 示例代码

下面是一个简单的示例，假设有一个名为departments的表，其结构如下：

CREATE TABLE departments (
  id INT,
  name VARCHAR(50),
  parent_id INT
);

该表用于存储部门信息，每个部门都有一个对应的父部门（除了最高层级的部门）。现在我们要查询某个部门及其所有子部门的信息。

4.1 创建测试数据

首先，我们需要向departments表中插入一些测试数据：

INSERT INTO departments (id, name, parent_id) VALUES (1, '总部', NULL);
INSERT INTO departments (id, name, parent_id) VALUES (2, '财务部', 1);
INSERT INTO departments (id, name, parent_id) VALUES (3, '人力资源部', 1);
INSERT INTO departments (id, name, parent_id) VALUES (4, '财务管理部', 2);
INSERT INTO departments (id, name, parent_id) VALUES (5, '人事部', 3);
INSERT INTO departments (id, name, parent_id) VALUES (6, '招聘部', 5);

4.2 查询指定部门及其子部门

下面的SQL语句将查询部门名称为’人力资源部’的部门及其所有子部门的信息：

WITH RECURSIVE dept_hierarchy AS (
  SELECT id, name, parent_id
  FROM departments
  WHERE name = '人力资源部'
  UNION ALL
  SELECT d.id, d.name, d.parent_id
  FROM departments d
  INNER JOIN dept_hierarchy dh ON dh.id = d.parent_id
)
SELECT * FROM dept_hierarchy;

这段SQL语句分为两部分，第一部分使用WITH RECURSIVE定义了一个临时表dept_hierarchy，用于存储递归查询的结果。在这个临时表中，首先选择名称为’人力资源部’的部门作为初始结果。

第二部分将departments表与临时表dept_hierarchy进行关联查询，通过INNER JOIN关键字将父部门和子部门进行连接。使用UNION ALL将每次查询的结果合并到临时表中，实现逐层递归查询。

最后，在外层通过SELECT * FROM dept_hierarchy来查询最终的结果，即’人力资源部’及其所有子部门的信息。

4.3 运行结果

运行上述示例代码后，将会得到如下查询结果：

+------+------------+-----------+
| id   | name       | parent_id |
+------+------------+-----------+
| 3    | 人力资源部 | 1         |
| 5    | 人事部     | 3         |
| 6    | 招聘部     | 5         |
+------+------------+-----------+

4.4 改进递归查询条件

如果需要查询所有部门的信息，不仅限于指定的部门及其子部门，只需将递归查询条件改为WHERE parent_id IS NULL即可：

WITH RECURSIVE dept_hierarchy AS (
  SELECT id, name, parent_id
  FROM departments
  WHERE parent_id IS NULL
  UNION ALL
  SELECT d.id, d.name, d.parent_id
  FROM departments d
  INNER JOIN dept_hierarchy dh ON dh.id = d.parent_id
)
SELECT * FROM dept_hierarchy;

这样就可以查询到所有部门的信息。