MySQL-8 公用表表达式CTE的使用方法实例分析

公用表表达式CTE就是命名的临时结果集，作用范围是当前语句。

说白了，你可以把它理解成一个可以多次重复使用的子查询，当然跟子查询还是有点区别的，CTE可以引用其他CTE，但子查询不能引用其他子查询。

自MySQL 8.0版以来简要介绍了公共表表达式或叫CTE的功能，因此需要您在计算机上安装MySQL 8.0，因此以下语句仅适合在 MySQL8.0 以上版本。

一、CTE 表达式

1、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的查询的列列表。

2、简单的MySQL CTE示例

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

参见另外一个例子：

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

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

3. 更高级的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的数据。

以上是一些简单的使用。还有其他的使用方式。