MYSQL CTE

１.　CTE是什么

从Mysql 8.x开始支持公用表达式（简称CTE）。CTE是一个命名的临时结果集，它不作为对象存储，仅在查询执行期间持续。有点类似子查询,把C查询的结果作为中间表，但和子查询不同的是，CTE查询结果可以被多次使用。

２.　CTE语法

WITH cte_name_1 (column_list) AS (subquery)
    [, cte_name_2 (column_list) AS (subquery)] 
SELECT column_list FROM cte_name;

请注意，查询中的列数必须与column_list中的列数相同。 如果省略column_list，CTE将使用定义CTE的查询的列列表。复杂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;