MySQL 公用表表达式(CTE)
在MySQL中，每个语句或查询都会产生一个临时结果或关系。通用表表达式或CTE用于
命名该特定语句的执行范围内的那些临时结果集，例如CREATE，插入， SELECT ，更新，删除，等等。
有些与CTE相关的关键点是:
它是通过使用 WITH 子句定义的。
通过WITH子句，我们可以在单个查询中指定多个CTE。
CTE可以引用属于同一WITH子句的其他CTE，但是这些CTE应该更早地定义。
CTE的执行范围存在于使用它的特定语句中。
MySQL CTE语法
MySQL CTE的语法包括名称，可选的列列表以及定义公用表表达式(CTE)的语句/查询。定义CTE之后，我们可以将其用作SELECT，INSERT，UPDATE和DELETE查询中的视图。
以下是 MySQL :
WITH cte_name (column_names) AS (query)
SELECT * FROM cte_name;
这是为了确保CTE参数中的列数必须与查询中的列数相同。如果我们尚未在CTE参数中定义列，它将使用定义CTE的查询列。
类似于派生表，它不能存储为对象，一旦丢失就将丢失查询执行完成。与派生表相比，CTE提供更好的可读性，并且还提高了性能。
与派生表不同，CTE是
子查询，可以
自引用使用自己的名称。它也称为
递归CTE ，也可以在同一查询中多次引用。
与递归CTE相关的一些要点是:
它是使用WITH RECURSIVE子句定义的。
递归CTE必须包含终止条件。
我们将使用递归CTE进行系列生成和遍历分层或树状数据。
MySQL递归CTE语法
以下是MySQL中递归CTE的基本语法:
WITH RECURSIVE cte_name (column_names) AS ( subquery )
SELECT * FROM cte_name;
在这里，子查询是一个MySQL查询，它使用cte_name作为自己的名称进行引用。
MySQL CTE示例
让我们了解CTE的方式使用各种示例在MySQL中工作。在这里，我们将使用表
"员工" 进行演示。假设该表包含以下数据:
执行下面的陈述了解CTE的概念。在此示例中，CTE名称为
employee_in_california ，定义CTE的子查询返回emp_name，emp_age和city这三列。因此，CTE employee_in_california将返回位于
加利福尼亚城市的所有员工。
定义CTE employee_in_california后，我们已在
SELECT 中引用了该员工>仅选择位于加利福尼亚州的员工的声明。
WITH employees_in_california AS (
SELECT * FROM employees WHERE city = 'California'
)
SELECT emp_name, emp_age, city FROM employees_in_california
WHERE emp_age >= 32 ORDER BY emp_name;
执行上述语句后，将给出以下输出。在这里，我们可以看到结果仅返回位于加利福尼亚的员工数据。
更高级的MySQL CTE示例
假设我们有一个名为
customer 和
order ，其中包含以下数据:
表: customer
表: 订单
请参阅下面的语句，该语句使用
INNER JOIN 子句。
WITH total_customer_2020 AS (
SELECT cust_id, name, occupation FROM customer
INNER JOIN orders USING (cust_id)
ORDER BY age
)
SELECT * FROM orders JOIN total_customer_2020 USING (cust_id);
执行后，我们将获得以下输出:
MySQL递归CTE示例
以下示例说明了递归CTE的工作方式。考虑下面的语句，该语句生成一系列的
前五个奇数:
WITH RECURSIVE
odd_num_cte (id, n) AS
(
SELECT 1, 1
union all
SELECT id+1, n+2 from odd_num_cte where id < 5
)
SELECT * FROM odd_num_cte;
执行上面的语句后，将给出如下输出:
以上语句由两部分组成: 一个是非递归的，另一个是递归的。
非递归: SELECT 1，1
此部分将生成带有两列的初始行，分别为" id"和" n"，以及单行。
递归: SELECT id + 1，n + 2 from odd_num_cte，其中id <5
该部分负责将行添加到先前的输出中，直到不满足终止条件(id <5)。当id达到5时，条件变为假，并且递归过程终止。
WITH子句使用
MySQL提供了许多上下文来使用WITH子句用于创建CTE。让我们一个个地详细讨论。
首先，我们可以在SELECT，UPDATE和DELETE查询的开头使用WITH子句，如下所示。
WITH ... SELECT ...
WITH ... UPDATE ...
WITH ... DELETE ...
第二，我们可以在子查询或派生表子查询的开头使用WITH子句，如下所示:
SELECT ... WHERE id IN (WITH ... SELECT ...);
SELECT * FROM (WITH ... SELECT ...) AS derived_table;
第三，我们可以在包含SELECT子句的SELECT语句的紧前面使用WITH子句，如下所示:
CREATE TABLE ... WITH ... SELECT ...
CREATE VIEW ... WITH ... SELECT ...
INSERT ... WITH ... SELECT ...
REPLACE ... WITH ... SELECT ...
DECLARE CURSOR ... WITH ... SELECT ...
EXPLAIN ... WITH ... SELECT ...
使用CTE的好处
它提供了更好的查询可读性。
它提高了查询的性能。
CTE允许我们将其用作VIEW概念的替代方式
它还可以用作CTE的链接以简化查询。
它还可用于轻松实现递归查询。
