Skip to main content

MySQL-CTE简介

什么是通用表表达式或CTE

公共表表达式是一个命名的临时结果集,它只存在于单个SQL语句的执行范围内,例如SELECT、INSERT、UPDATE或DELETE。

与派生表类似,CTE不作为对象存储,只在执行查询时才有效。

与派生表不同,CTE可以是自引用(递归CTE),也可以在同一查询中多次引用。此外,与派生表相比,CTE提供了更好的可读性和性能。

MySQL CTE语法

CTE的结构包括名称、可选列列表和定义CTE的查询。定义CTE后,可以将其用作SELECT、INSERT、UPDATE、DELETE或CREATE view语句中的视图。

以下说明了CTE的基本语法:

WITH cte_name (column_list) AS (
query
)
SELECT * FROM cte_name;

请注意,查询中的列数必须与列列表中的列数相同。如果省略列_列表,CTE将使用定义CTE的查询的列列表

简单的MySQL CTE示例

我们将使用示例数据库中的customers表进行演示:

下面的示例演示了如何使用CTE从示例数据库中的customers表中查询数据。

请注意,本示例仅用于演示目的,以便于您理解CTE概念。

WITH customers_in_usa AS (
SELECT
customerName, state
FROM
customers
WHERE
country = 'USA'
) SELECT
customerName
FROM
customers_in_usa
WHERE
state = 'CA'
ORDER BY customerName;

MySQL CTE Example 1

在本例中,CTE的名称是customers_In_usa,定义CTE的查询返回两列customerName和state。因此,美国CTE中的客户返回位于美国的所有客户。

在美国CTE中定义了客户后,我们在SELECT语句中引用了它,以仅选择位于加利福尼亚州的客户。

请参见以下示例:

WITH topsales2003 AS (
SELECT
salesRepEmployeeNumber employeeNumber,
SUM(quantityOrdered * priceEach) sales
FROM
orders
INNER JOIN
orderdetails USING (orderNumber)
INNER JOIN
customers USING (customerNumber)
WHERE
YEAR(shippedDate) = 2003
AND status = 'Shipped'
GROUP BY salesRepEmployeeNumber
ORDER BY sales DESC
LIMIT 5
)
SELECT
employeeNumber,
firstName,
lastName,
sales
FROM
employees
JOIN
topsales2003 USING (employeeNumber);

MySQL CTE Example 2

在本例中,CTE返回2003年排名前五的销售代表。之后,我们参考了topsales2003 CTE,以获取有关销售代表的其他信息,包括名字和姓氏。

一个更高级的MySQL CTE示例

请参见以下示例:

WITH salesrep AS (
SELECT
employeeNumber,
CONCAT(firstName, ' ', lastName) AS salesrepName
FROM
employees
WHERE
jobTitle = 'Sales Rep'
),
customer_salesrep AS (
SELECT
customerName, salesrepName
FROM
customers
INNER JOIN
salesrep ON employeeNumber = salesrepEmployeeNumber
)
SELECT
*
FROM
customer_salesrep
ORDER BY customerName;

MySQL CTE Example 3

在这个例子中,我们在同一个查询中有两个CTE。第一个CTE(salesrep)得到的是以销售代表为职衔的员工。第二个CTE(customer_salesrep)引用内部联接子句中的第一个CTE,以获取销售代表和每个销售代表负责的客户。

在拥有第二个CTE之后,我们使用带有ORDER BY子句的简单SELECT语句从该CTE查询数据。

带用法的从句

有一些上下文可以使用WITH子句生成公共表表达式:

首先,可以在SELECT、UPDATE和DELETE语句的开头使用WITH子句:

WITH ... SELECT ...
WITH ... UPDATE ...
WITH ... DELETE ...

其次,WITH子句可用于子查询或派生表子查询的开头:

SELECT ... WHERE id IN (WITH ... SELECT ...);

SELECT * FROM (WITH ... SELECT ...) AS derived_table;

第三,WITH子句可以直接用于包含SELECT子句的SELECT语句之前:

CREATE TABLE ... WITH ... SELECT ...
CREATE VIEW ... WITH ... SELECT ...
INSERT ... WITH ... SELECT ...
REPLACE ... WITH ... SELECT ...
DECLARE CURSOR ... WITH ... SELECT ...
EXPLAIN ... WITH ... SELECT ...

在本教程中,您学习了如何使用MySQL CTE简化复杂的查询。