MySQL8 CTE解决不定层级树形迭代问题

MySQL Common Table Expressions（CTE，公用表表达式）是在MySQL 8.0及更高版本中引入的一种高级SQL构造，它允许用户定义一个临时的结果集，这个结果集可以在同一个查询中被多次引用，从而简化复杂的查询逻辑和提高代码的可读性。通过 WITH 关键字定义，可以把复杂的子查询抽象出来，给其命名，提高查询的可读性和可维护性。在同一个查询中，可以多次引用同一个 CTE，无需重复书写相同子查询。

作用：

临时结果集定义：CTE允许你在查询中定义一个临时的、只在当前查询上下文中存在的中间结果集，就像一个临时的视图一样，但它不会持久化存储在数据库中。
递归查询支持：特别地，MySQL CTE还支持递归查询，这对于处理层次型数据（如组织架构、菜单树等）非常有用，能够方便地实现树形结构的遍历和展现。
查询复用：你可以在一个查询的不同部分多次引用同一个CTE，避免了在多个地方重复相同的子查询，减少了代码冗余。
查询分解：将复杂的查询逻辑分解成易于理解的部分，每一部分作为一个单独的CTE，这样可以增强查询的模块化和维护性。

下面案例说明运用CTE实现递归查询，创建表和插入数据：

CREATE TABLE employees (emp_id INT PRIMARY KEY,emp_name VARCHAR(100),manager_id INT,FOREIGN KEY (manager_id) REFERENCES employees(emp_id)
);INSERT INTO employees VALUES 
(1, 'CEO', NULL),
(2, 'Manager A', 1),
(3, 'Employee 1', 2),
(4, 'Employee 2', 2),
(5, 'Manager B', 1),
(6, 'Employee 3', 5);INSERT INTO employees VALUES 
(7, 'Employee 4', 2),
(8, 'Employee 5', 2),
(9, 'Employee 6', 5),
(10, 'Employee 7', 5),
(11, 'Manager C', 1),
(12, 'Employee 8', 11),
(13, 'Employee 9', 11),
(14, 'Employee 10', 11),
(15, 'Manager D', 1),
(16, 'Employee 11', 15),
(17, 'Employee 12', 15),
(18, 'Employee 13', 15),
(19, 'Manager E', 1),
(20, 'Employee 14', 19),
(21, 'Employee 15', 19),
(22, 'Employee 16', 2),
(23, 'Employee 17', 5),
(24, 'Employee 18', 11),
(25, 'Employee 19', 15),
(26, 'Employee 20', 19);

查询SQL：

-- 定义一个名为org_structure的递归公共表表达式（CTE），用于构建组织结构层次
WITH RECURSIVE org_structure AS (-- 初始化：选取公司最高级别（CEO）的员工信息SELECT emp_id, emp_name, manager_id, 1 AS level , CONCAT('',emp_id) as pathFROM employeesWHERE emp_name = 'CEO'UNION ALL-- 递归部分：通过JOIN操作连接employees表和已生成的org_structure表，获取下一级别的员工信息SELECT e.emp_id, e.emp_name, e.manager_id, os.level + 1 , CONCAT(os.path,',',e.emp_id) FROM employees eJOIN org_structure os ON e.manager_id = os.emp_id
)
-- 最终查询结果：从org_structure CTE中选择需要展示的字段，并按照层级(level)和员工ID(emp_id)排序
SELECT emp_id, emp_name, manager_id, level ,path
FROM org_structure
ORDER BY level, emp_id;

查询SQL解析：

通过WITH关键字定义了CTE（公共表达式块），同时增加递归关键字（RECURSIVE）。之所以增加递归，是因为需要在不定层级的情况下，一直深挖，直到没有数据为止。

递归的核心有两个规则：

1.因为要进行不定层级的递归，需要给出一个根节点。上面的SQL中是以‘CEO’为根节点。

2.进行递归的连接，对应UNION ALL下面的部分，用employees表与递归上一层的结果进行关联，关联条件是员工表中的manager_id与递归结果中的emp_id进行关联。

找到的数据会使用UNION ALL进行连接，只要manager_id与emp_id匹配的到数据，条件就会成立，一直会递归下去，直到匹配结束。

查询结果：