mysql8递归查询
WITH RECURSIVE field_hierarchy AS (
-- 查询根菜单（parentID = 0）
SELECT fid, subjectName, parentId, 0 AS level
FROM tbl_report_all_field
WHERE parentId = 0
UNION ALL
-- 递归查询子菜单
SELECT m.fid, m.subjectName, m.parentId, mh.level + 1 AS level
FROM tbl_report_all_field m
JOIN field_hierarchy mh ON m.parentId = mh.fid
)
-- 显示递归查询的中间结果
SELECT fid, subjectName, parentId, level
FROM field_hierarchy
ORDER BY level, parentID, fid
在 MySQL 8 中，递归查询（例如树形结构的查询）可以通过 公共表表达式（CTE） 来实现。MySQL 8 引入了对递归公共表表达式（Recursive CTE）的支持，使得处理层级数据（例如组织结构、评论树等）变得更加方便。
递归查询示例：树形结构
假设我们有一个员工表 employees，记录了员工的 id、name 和 manager_id，其中 manager_id 指向的是该员工的上级经理的 id。
sql
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
manager_id INT,
FOREIGN KEY (manager_id) REFERENCES employees(id)
);
INSERT INTO employees (id, name, manager_id) VALUES
(1, 'Alice', NULL), -- Alice is the CEO, no manager
(2, 'Bob', 1), -- Bob is managed by Alice
(3, 'Charlie', 1), -- Charlie is managed by Alice
(4, 'David', 2), -- David is managed by Bob
(5, 'Eve', 2), -- Eve is managed by Bob
(6, 'Frank', 3); -- Frank is managed by Charlie
这个表描述了一个公司的员工结构，其中 manager_id 用来标识一个员工的直接上级。
递归查询树形结构
如果我们想要查询某个员工的所有下属，或者查询整个公司的层级结构，可以使用递归查询。
1. 查询整个公司的层级结构
我们可以使用递归公共表表达式（CTE）来查询从某个员工（比如 Alice）开始的整个下属层级结构。
sql
WITH RECURSIVE employee_hierarchy AS (
-- 基础查询：选择根节点（Alice，id = 1）
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL -- Alice 是顶级经理
UNION ALL
-- 递归查询：选择直接下属
SELECT e.id, e.name, e.manager_id, eh.level + 1 AS level
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
-- 查询整个组织结构
SELECT id, name, manager_id, level
FROM employee_hierarchy
ORDER BY level, manager_id, id;
解释：
WITH RECURSIVE 关键字开始了递归查询。
第一个查询部分是基础查询，从根节点（manager_id IS NULL，即 Alice）开始。
UNION ALL 后面的部分是递归查询，逐步查找每个员工的下属（通过 manager_id = eh.id 来连接）。
每次递归时，我们通过 level + 1 来表示当前员工的层级。
查询结果：
plaintext
+----+---------+------------+-------+
| id | name | manager_id | level |
+----+---------+------------+-------+
| 1 | Alice | NULL | 1 |
| 2 | Bob | 1 | 2 |
| 3 | Charlie | 1 | 2 |
| 4 | David | 2 | 3 |
| 5 | Eve | 2 | 3 |
| 6 | Frank | 3 | 3 |
+----+---------+------------+-------+
这个查询结果展示了公司的层级结构，其中 level 列表示员工所在的层级。
2. 查询某个员工（例如 Alice）及其所有下属
如果我们只想查询某个员工及其下属的层级结构，可以修改递归查询的起点。例如，查询 Alice（id = 1）及其所有下属：
sql
WITH RECURSIVE employee_hierarchy AS (
-- 基础查询：选择 Alice 作为起点
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE id = 1 -- Alice 的 ID
UNION ALL
-- 递归查询：选择 Alice 的直接下属
SELECT e.id, e.name, e.manager_id, eh.level + 1 AS level
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
-- 查询 Alice 及其下属
SELECT id, name, manager_id, level
FROM employee_hierarchy
ORDER BY level, manager_id, id;
查询结果：
plaintext
+----+---------+------------+-------+
| id | name | manager_id | level |
+----+---------+------------+-------+
| 1 | Alice | NULL | 1 |
| 2 | Bob | 1 | 2 |
| 3 | Charlie | 1 | 2 |
| 4 | David | 2 | 3 |
| 5 | Eve | 2 | 3 |
+----+---------+------------+-------+
这次结果只包含 Alice 以及她的所有直接和间接下属。
关键概念：
递归公共表表达式（CTE）：MySQL 8 引入了递归查询的支持，使用 WITH RECURSIVE 语句定义递归查询。
递归查询：通过 UNION ALL 连接基础查询和递归查询，实现层级数据的提取。
level 字段：表示当前员工在树中的层级。
总结：
在 MySQL 8 中，递归查询是处理树形结构（如员工层级、组织结构、评论回复等）的强大工具。通过使用递归公共表表达式（CTE），我们可以方便地查询树形数据及其层级关系。
