7.3 公共表表达式

在本教程中，您将学习如何使用MySQL CTE或公用表表达式以更可读的方式构建复杂查询。

自MySQL 8.0版以来简要介绍了公共表表达式或叫CTE的功能，因此需要您在计算机上安装MySQL 8.0，以便在本教程中练习本语句。

1. 什么是公用表表达式或CTE？

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

与派生表类似，CTE不作为对象存储，仅在查询执行期间持续。 与派生表不同，CTE可以是自引用(递归CTE)，也可以在同一查询中多次引用。 此外，与派生表相比，CTE提供了更好的可读性和性能。

2. 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的查询的列列表。

3. 简单的MySQL CTE示例

以下示例说明如何使用CTE查询示例数据库(yiibaidb)中的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;

注意：上面语句只能在 MySQL8.0 以上版本才支持。

执行上面查询语句，得到以下结果(部分)

img

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

在定义美国CTE的客户之后，我们可在SELECT语句中引用它，例如，仅查询选择位于California 的客户。

参见另外一个例子：

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

执行上面查询后，得到以下结果 -

img

在这个例子中，CTE中返回了在2013年前五名的销售代表。之后，我们引用了topsales2013 CTE来获取有关销售代表的其他信息，包括名字和姓氏。

4. 更高级的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;

执行上面查询语句，得到以下结果 -

img

在这个例子中，在同一查询中有两个CTE。 第一个CTE(salesrep)获得职位是销售代表的员工。 第二个CTE(customer_salesrep)使用INNER JOIN子句与第一个CTE连接来获取每个销售代表负责的客户。

在使用第二个CTE之后，使用带有ORDER BY子句的简单SELECT语句来查询来自该CTE的数据。

5. WITH子句用法

有一些上下文可以使用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语句之前立即使用WITH子句，包括SELECT子句：

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

在本教程中，您已经学会了如何使用MySQL 公共表表达式(CTE)来构造复杂的查询语句。