第48讲：MySQL的公用表达式

公用表达式

公用表达式的介绍：

公用表达式是MySQL8.0的新特性，它是一个命名的临时结果集，作用范围是当前语句。

可以理解为复用的子查询，当然跟子查询还是有区别。例如：公用表达式可以引用公用表达式，但是子查询不能引用子查询。

依据语法结构和执行方式不同，公用表达式可以分为普通公用表达式和递归公用表达式。

普通公用表达式

语法结构：

with 名称
as
(子查询)


select|delete|update语句;

举栗子：

#公用表达式，部门表
with cte_emp
as (select distinct department_id from employees)


#员工表跟公用表达式，连表查询部门名称
select *
from cte_emp e
inner join departments d on e.department_id=d.department_id;

递归公表达式

可以自己调用自己

语法结构：

with recursive 名称 
as (子查询)


select|update|delete 语句;

递归公用表达式由两部分组成，分别是种子查询和递归查询。中间通过union all 进行连接。

种子查询就是递归的初始值。这个查询只会运行一次，之后递归查询会一直执行，直到没有任何新的数据产生，才结束。

举栗子：

递归找出员工表中的每个管理层级的员工。种子查询，找出公司最大的领导，后面根据最大的领导id，查找所有第二层的领导id，然后用第二层的所有领导id，找第三层，直到最后一层没有下属，查不到数据结束。

with recursive cte_emp as
(
#种子查询,找出最大的领导
select employee_id,last_name,manager_id,1 as n
from employees where employee_id=100
union all
#递归查询
select e.employee_id,e.last_name,e.manager_id,n+1 as n
from employees e inner join cte_emp t
on e.manager_id=t.employee_id
)


select * from cte_emp;

总结

公用表达式作用可以代替子查询，而且可以被多次引用。

递归公用表达式对查询有一个共同的根节点的树形结构数据非常有效，可以轻松搞定其他方式难以处理的查询。

推荐阅读书籍