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;

---

简单的MySQL CTE示例

以下示例说明如何使用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;

运行结果：

+------------------------------+
| customerName                 |
+------------------------------+
| Boards & Toys Co.            |
| Collectable Mini Designs Co. |
| Corporate Gift Ideas Co.     |
| Men 'R' US Retailers, Ltd.   |
| Mini Gifts Distributors Ltd. |
| Mini Wheels Co.              |
| Signal Collectibles Ltd.     |
| Technics Stores Inc.         |
| The Sharp Gifts Warehouse    |
| Toys4GrownUps.com            |
| West Coast Collectables Co.  |
+------------------------------+
11 rows in set (0.25 sec)

在此示例中，CTE的名称是customers_in_usa，定义CTE的查询返回两列customerName和state。因此，customers_in_usaCTE返回位于美国的所有客户。 在定义customers_in_usaCTE之后，我们在SELECT语句中引用它同时查询“CA”的客户。 看另一个例子：

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);

运行结果：

+----------------+-----------+-----------+-----------+
| employeeNumber | firstName | lastName  | sales     |
+----------------+-----------+-----------+-----------+
|           1165 | Leslie    | Jennings  | 413219.85 |
|           1370 | Gerard    | Hernandez | 295246.44 |
|           1401 | Pamela    | Castillo  | 289982.88 |
|           1621 | Mami      | Nishi     | 267249.40 |
|           1501 | Larry     | Bott      | 261536.95 |
+----------------+-----------+-----------+-----------+
5 rows in set (0.17 sec)

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

---

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;

运行结果：

+------------------------------------+------------------+
| customerName                       | salesrepName     |
+------------------------------------+------------------+
| Alpha Cognac                       | Gerard Hernandez |
| Amica Models & Co.                 | Pamela Castillo  |
| Anna's Decorations, Ltd            | Andy Fixter      |
| Atelier graphique                  | Gerard Hernandez |
| Australian Collectables, Ltd       | Andy Fixter      |
| Australian Collectors, Co.         | Andy Fixter      |
| Australian Gift Network, Co        | Andy Fixter      |
| Auto Associs & Cie.                | Gerard Hernandez |
...

在此示例中，我们在同一查询中有两个CTE。第一个CTE（  salesrep）获得职称为销售代表的员工。第二个CTE（customer_salesrep）参考INNER JOIN 子句中的第一个CTE，以获得销售代表和每个销售代表负责的客户。 在进行第二次CTE之后，我们使用SELECT带有ORDER BY子句的简单语句从CTE查询数据。

---

WITH子句用法

有一些上下文可以使用WITH子句来创建公用表表达式： 首先，WITH子句可以在开始时使用SELECT，UPDATE和DELETE语句：

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构建复杂查询。

---