MySQL CTE

在MySQL中，每个语句或查询都产生一个临时结果或关系。常用的表达式或CTE用于给那些存在于特定语句的执行范围内的临时结果集命名，比如CREATE、INSERT、SELECT、UPDATE、DELETE等。

与CTE相关的一些关键点包括：

• 通过使用 WITH 子句来定义。
• WITH子句允许在单个查询中指定多个CTE。
• CTE可以引用与同一个WITH子句的其他CTE，但这些CTE应该在之前定义。
• CTE的执行范围仅限于使用它的特定语句内部。

MySQL CTE语法

MySQL CTE的语法包括名称、可选的列列表和定义常用表达式(CTE)的语句/查询。在定义CTE之后，我们可以将其作为SELECT、INSERT、UPDATE和DELETE查询中的视图使用。

以下是MySQL中CTE的基本语法：

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示例

让我们通过各种示例来了解MySQL中的CTE是如何工作的。在这里，我们将使用一个表 “employees” 进行演示。假设这个表包含以下数据：

执行以下语句以理解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 子句的高级CTE示例。

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 where id < 5 **

这部分负责在前一个输出中添加行，直到不满足终止条件（id < 5）。当id达到5时，条件变为false，递归过程终止。

The WITH clause uses

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链接。
• 它还可以用于轻松实现递归查询。

本文链接：http://so.lmcjl.com/news/15825/