MySQL CTE语法

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

下面说明了CTE的基本语法：

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

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

什么是通用表表达式或CTE

公用表表达式是一个命名的临时结果集，该结果集仅存在于单个SQL语句的执行范围内，例如SELECT，INSERT，UPDATE或DELETE。

与派生表类似，CTE不存储为对象，仅在查询执行期间存储。

与派生表不同，CTE可以是自引用(递归CTE)，也可以在同一查询中多次引用。
另外，与派生表相比，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;

在此示例中，我们在同一查询中有两个CTE。
第一个CTE(salesrep)获得了其职务为销售代表的员工。
第二个CTE(customer_salesrep)在INNER JOIN子句中引用第一个CTE，以获取销售代表和每个销售代表负责的客户。

在拥有第二个CTE之后，我们使用带有ORDER BY子句的简单SELECT语句从该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;

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

定义了customer_in_usa 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);

在此示例中，CTE返回20​​03年销售排名前5名的销售代表。
此后，我们引用了topsales2003 CTE以获取有关销售代表的其他信息，包括名字和姓氏。

WITH子句用法

您可以在某些上下文中使用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;

第三，可以在包含SELECT子句的语句的SELECT紧前面使用WITH子句：

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

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

简介：在本教程中，您将学习如何使用MySQL CTE或通用表表达式以更易读的方式构造复杂的查询。

MySQL从8.0版本开始引入了通用表表达式或CTE功能，因此您应该拥有MySQL 8.0+才能使用本教程中的语句。