探究MySQL递归CTE：从简单查询到复杂层级关系查询

MySQL中可以使用递归CTE(Common Table Expression)来实现递归查询。递归CTE是指在查询语句中递归引用自己的表达式，常用于处理树形结构的数据。

下面是一个示例，假设我们有一个员工表（employees），其中每个员工都有一个上级（manager_id）：

CREATE TABLE employees (
  id INT NOT NULL,
  name VARCHAR(50),
  manager_id INT,
  PRIMARY KEY (id)
);

INSERT INTO employees (id, name, manager_id) VALUES 
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 2),
(4, 'Dave', 2),
(5, 'Eve', 1),
(6, 'Frank', 5),
(7, 'Grace', 6);

往下查询

我们可以使用递归CTE来查询某个员工的所有下属：

WITH RECURSIVE subordinates AS (
  SELECT id, name, manager_id
  FROM employees
  WHERE id = 2  -- 以Bob为例
  UNION
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

在这个查询中，我们使用了WITH RECURSIVE关键字来定义递归CTE，命名为subordinates。我们首先从employees表中选出id为2的员工（即Bob），并将其加入到递归结果集中。然后，我们递归地查询所有直接下属的员工（即manager_id等于上一层结果集中员工的id），并将它们加入到结果集中。这个过程会一直重复，直到没有新的下属被加入到结果集中为止。

上面的查询将会返回Bob所有下属的信息：

+----+---------+------------+
| id | name    | manager_id |
+----+---------+------------+
| 3  | Charlie | 2          |
| 4  | Dave    | 2          |
+----+---------+------------+

注意，在递归CTE中必须至少包含一个基础查询（即不递归的查询），否则会产生死循环。在这个示例中，我们首先选出了一个特定的员工，作为递归CTE的基础查询。如果我们不指定基础查询，那么递归CTE会一直重复递归，导致死循环。

此外，递归CTE中的第一个查询结果必须与后面的查询结果拥有相同的列名和数据类型，否则会导致查询失败。

希望这个示例能够帮助你理解在MySQL中如何使用递归CTE进行递归查询。

往上查询

如果你想查询员工id为3上面所有的人员，可以使用和前面相似的递归CTE查询来实现，示例如下：

WITH RECURSIVE superiors AS (
  SELECT id, name, manager_id
  FROM employees
  WHERE id = 3 -- 以Charlie为例
  UNION
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN superiors s ON e.id = s.manager_id
)
SELECT * FROM superiors;

这个查询中，我们将递归CTE命名为superiors，首先选出了id为3的员工（即Charlie），作为递归CTE的基础查询。然后，我们递归地查询所有直接或间接上级的员工（即id等于上一层结果集中员工的manager_id），并将它们加入到结果集中。这个过程会一直重复，直到没有新的上级被加入到结果集中为止。

当查询执行完成后，会返回员工id为3上面所有人员的信息。

注意，和之前相同，这个查询可能会返回整个员工表中所有的记录，因为每个员工都有一个上级，如果你的数据中有很多级嵌套的员工，递归查询的结果可能非常大，所以请谨慎使用。