mysql 递归查询理解

mysql支持递归查询在8.0版本，以前的不知道支不支持，在官网查询的是文档是8.0版本的。

这里以树形结构来做个备注，以备之后查询理解使用

以官网例子说明：

mysql> SELECT * FROM employees ORDER BY id;
+------+---------+------------+
| id   | name    | manager_id |
+------+---------+------------+
|   29 | Pedro   |        198 |
|   72 | Pierre  |         29 |
|  123 | Adil    |        692 |
|  198 | John    |        333 |
|  333 | Yasmina |       NULL |
|  692 | Tarek   |        333 |
| 4610 | Sarah   |         29 |
+------+---------+------------+

官方给出的例子如下：

WITH RECURSIVE employee_paths (id, name, path) AS
(
  SELECT id, name, CAST(id AS CHAR(200))
    FROM employees
    WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, CONCAT(ep.path, ',', e.id)
    FROM employee_paths AS ep JOIN employees AS e
      ON ep.id = e.manager_id
)
SELECT * FROM employee_paths ORDER BY path;

查询结果如下：

+------+---------+-----------------+
| id   | name    | path            |
+------+---------+-----------------+
|  333 | Yasmina | 333             |
|  198 | John    | 333,198         |
|   29 | Pedro   | 333,198,29      |
| 4610 | Sarah   | 333,198,29,4610 |
|   72 | Pierre  | 333,198,29,72   |
|  692 | Tarek   | 333,692         |
|  123 | Adil    | 333,692,123     |
+------+---------+-----------------+

生成的过程如下：

1.根据with递归语句中的第一个select语句，产生初始行（即第一行），即上述结果中id为333的那行，同时确定path的长度为char(200)，即生成表的格式和列名都是由第一行确定的，如果with后面没有传递列名的话。

 2.之后的每行生成都是由with语句的第二个select语句，第二个select语句查询原始表employees，查询manage_id为第一行生成的id为333的员工，并根据第二个select语句将原始表的id，name，path显示出来，即生成结果集中的第二行，id为198的那条数据

3.之后重复执行第二步，直到根据上一个查询结果，在原始表中找不到manage_id为123的结果，即退出递归查询。

如果要查询筛选结果，在select语句中增加一个where筛选语句即可。

mysql> WITH RECURSIVE ...
       ...
       SELECT * FROM employees_extended
       WHERE id IN (692, 4610)
       ORDER BY path;
+------+-------+-----------------+
| id   | name  | path            |
+------+-------+-----------------+
| 4610 | Sarah | 333,198,29,4610 |
|  692 | Tarek | 333,692         |
+------+-------+-----------------+

 

 关于with语句的理解，因为在使用过程中关于第二条语句select后面的列名是使用e还是ep，还有on后面的条件是e.id还是ep.id比较迷惑，故做个备注如下

WITH RECURSIVE employee_paths (id, name, path) AS
(
  SELECT id, name, CAST(id AS CHAR(200))
//非递归select生成初始列，即第一列，同时确定列名和列格式
    FROM employees
    WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, CONCAT(ep.path, ',', e.id)
    FROM employee_paths AS ep  
// 递归select生成第二列和之后的所有列，根据select后面的字段来计算生成之后的每条数据    
// ep即第二步生成查询中需要的的上一条语句生成结果
// 以生成表第二行数据为例，第二步会生成第二行，生成过程中的ep即为第一条数据，生成过程中的e即为查询表数据即employees
    JOIN employees AS e
// 生成的第二条数据，需要查询表中的e.manager_id等于第一条数据中的ep.id
      ON ep.id = e.manager_id
)
SELECT * FROM employee_paths ORDER BY path;

 

官方文档资料链接：https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive