Mysql基础篇（12）—— MySQL8.0新特性之公用表表达式

阿里云国内75折回扣微信号：monov8

阿里云国际，腾讯云国际，低至75折。AWS 93折免费开户实名账号代冲值优惠多多微信号：monov8 飞机：@monov6

又叫通用表表达式简称CTE(Common Table Expression)。CTE是一个命名的临时结果集作用范围是当前语句。CTE可以理解成一个可以复用的子查询当然跟子查询还是有点区别的CTE可以引用其他CTE但是子查询不能引用其他子查询。所以可以考虑代替子查询。

根据语法结构和执行方式不同共用表表达式分为普通公用表表达式和递归公用表表达式2种。

普通公用表表达式

语法结构

WITH CTE名称
AS (子查询)
SELECT|DELETE|UPDATE 语句;

比如查询员工所在部门的信息。

子查询

SELECT * FROM departments
WHERE department_id IN (
SELECT DISTINCT department_id
FROM employees);

公用表表达式

WITH emp_dept_id
AS (SELECT DISTINCT department_id FROM employees)
SELECT *
FROM departments d JOIN emp_dept_id e
ON d.department_id = e.department_id;

定义过公用表表达式之后的查询可以像表一样使用

递归公用表表达式

可以自己调用自己。语法结构

WITH RECURSIVE
CTE名称 AS (子查询)
SELECT|DELETE|UPDATE 语句;

递归公用表表达式由2部分组成分别是种子查询和递归查询中间通过关键字UNION[ALL]进行连接。这里的种子查询意思是获得递归的初始值。这个查询只会运行一次以创建初始数据集之后递归查询会一直执行直到没有任何新的查询数据产生递归返回。

比如有张employee表包含employee_idlast_name和manager_id三个字段。假设b是a的下属c是b的下属那么c是a的下下属。我们如何查出所有具有下下属身份的人员信息

以前我们得至少进行四次查询才能搞定

第一步先找出初代管理者将结果存入临时表。
第二步找出所有以初代管理者为管理者的人得到一个下属集把结果存入临时表。
第三步找出所有以下属为管理者的人得到一个下下属集把结果存入临时表。
第四步找出所有以下下属为管理的人得到一个结果集。

如果第四步的结果为空则计算结束否则就必须继续进行。一直到结果集为空为止。

使用公用表表达式的话只要一个sql就搞定了

WITH RECURSIVE cte
AS
(
    SELECT employee_id, last_name, manager_id, 1 AS n FROM employee WHERE employee_id = 100
    --种子查询找到第一代领导
    UNION ALL
    SELECT a.employee_id, a.last_name, a.manager_id, n+1 
    FROM employee AS A JOIN cte
    ON (a.manager_id = cte.employee_id) --递归查询找出以递归公用表表达式的人为领导的人
)
SELECT employee_id, last_name FROM cte WHERE n >= 3;

总之递归公用表表达式对于查询一个有共同的根节点的树形结构数据非常有用。它可以不受层级的限制轻松查出所有节点的数据。如果用其他的查询方式就比较复杂了。