MySQL CTE(公共表表示式)

在本教學中，您將學習如何使用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 以上版本才支援。

執行上面查詢語句，得到以下結果(部分) -

在此範例中，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);

執行上面查詢後，得到以下結果 -

在這個例子中，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;

執行上面查詢語句，得到以下結果 -

在這個例子中，在同一查詢中有兩個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)來構造複雜的查詢語句。