An Introduction to MySQL CTE

摘要：在本教程中，您将学习如何使用 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;
Code language: SQL (Structured Query Language) (sql)

请注意， query中的列数必须与column_list中的列数相同。如果省略column_list ，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;
Code language: SQL (Structured Query Language) (sql)

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

定义customers_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);
Code language: SQL (Structured Query Language) (sql)

在此示例中，CTE 返回 2003 年排名前 5 的销售代表。之后，我们引用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;
Code language: SQL (Structured Query Language) (sql)

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

获得第二个 CTE 后，我们使用带有ORDER BY子句的简单SELECT语句从该 CTE 查询数据。

WITH子句的用法

在某些上下文中，您可以使用WITH子句来创建公用表表达式：

首先， WITH子句可以用在SELECT 、 UPDATE和DELETE语句的开头：

WITH ... SELECT ...
WITH ... UPDATE ...
WITH ... DELETE ...
Code language: SQL (Structured Query Language) (sql)

其次， WITH子句可以用在子查询或派生表子查询的开头：

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

SELECT * FROM (WITH ... SELECT ...) AS derived_table;
Code language: SQL (Structured Query Language) (sql)

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

CREATE TABLE ... WITH ... SELECT ...
CREATE VIEW ... WITH ... SELECT ...
INSERT ... WITH ... SELECT ...
REPLACE ... WITH ... SELECT ...
DECLARE CURSOR ... WITH ... SELECT ...
EXPLAIN ... WITH ... SELECT ...
Code language: SQL (Structured Query Language) (sql)

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