一、公用表表达式概述

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

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

二、普通公用表表达式

WITH CTE名称 
AS （子查询）
SELECT|DELETE|UPDATE 语句;

普通公用表表达式类似于子查询，不过，跟子查询不同的是，它可以被多次引用，而且可以被其他的普通公用表表达式所引用。

example:

CREATE DATABASE test17;
USE test17;

CREATE TABLE employees
AS
SELECT * FROM atguigudb.employees;

CREATE TABLE departments
AS
SELECT * FROM atguigudb.departments;

WITH cte_emp
AS (
	SELECT DISTINCT department_id
	FROM employees
)

SELECT * 
FROM departments d JOIN cte_emp e
ON d.department_id = e.department_id;

三、递归公用表表达式

??递归公用表表达式也是一种公用表表达式，只不过，除了普通公用表表达式的特点以外，它还有自己的特点，就是可以调用自己。它的语法结构是：

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

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

example:

WITH RECURSIVE cte
AS(
	SELECT employee_id,last_name,manager_id, 1 AS n 
	FROM employees WHERE employee_id = 100
	UNION ALL
	SELECT a.employee_id,a.last_name,a.manager_id,n+1 
	FROM employees AS a JOIN cte 
	ON (a.manager_id = cte.employee_id)
)

SELECT employee_id,last_name FROM cte WHERE n >= 3;

四、小结

??公用表表达式的作用是可以替代子查询，而且可以被多次引用。递归公用表表达式对查询有一个共同根节点的树形结构数据非常高效，可以轻松搞定其他查询方式难以处理的查询。