SQL - 公共表表達式 (CTE)

---

---

公共表表達式 (CTE) 可以透過使複雜查詢更易於閱讀和簡單來簡化複雜查詢的管理和編寫，就像資料庫檢視和派生表一樣。我們可以透過將複雜查詢分解成簡單的塊來重用或重寫查詢。

SQL 公共表表達式

MySQL 中的 WITH 子句用於指定公共表表達式。

SQL 中的公共表表達式 (CTE) 是一次性結果集，即它是一個僅在單個查詢執行期間存在的臨時表。它允許我們在該查詢中專門處理資料，例如在 SELECT、UPDATE、INSERT、DELETE、CREATE、VIEW 或 MERGE 語句中使用它。

CTE 是臨時的，因為它無法儲存在任何地方以供以後使用；一旦查詢執行完畢，它就會丟失。

MySQL WITH 子句

要指定公共表表達式，我們使用包含一個或多個以逗號分隔的子句的 WITH 子句。在每個子句中，我們可以提供一個產生結果集的子查詢，併為該子查詢分配一個名稱。

在 8.0 之前的 MySQL 版本中，您無法使用 WITH 子句。

語法

以下是使用 WITH 子句建立 CTE 的語法：

WITH CTE_NAME (column_name) AS (query)
SELECT * FROM CTE_NAME;

其中，

• CTE_NAME − 它是在 CTE 中分配給 CTE 的名稱。
• column_name − 它是 CTE 的列名，這對於提高查詢的可讀性很有用。
• query − 它定義 CTE，它可以是任何有效的 SQL 查詢。
• 定義 CTE 後，您可以在同一會話中的後續查詢中引用它。

示例

假設我們在 MySQL 資料庫中使用 CREATE TABLE 語句建立了一個名為 CUSTOMERS 的表，如下所示：

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

現在，我們正在向上面建立的表中插入一些記錄：

INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

建立的表如下所示：

在這裡，我們正在建立一個名為 CUSTOMER_AGE 的公共表表達式 (CTE)，它選擇所有年齡為 23 的客戶。然後，我們從 CTE 中檢索這些客戶的 ID、姓名和年齡。

WITH CUSTOMER_AGE AS (
SELECT * FROM customers WHERE AGE = 23)
SELECT ID, NAME, AGE FROM CUSTOMER_AGE;

輸出

以下是上述查詢的輸出：

來自多個表的 CTE

我們還可以建立一個公共表表達式 (CTE)，它透過在 CTE 的子查詢中使用 JOIN 操作來組合來自多個表的資料。為此，我們需要使用逗號運算子分隔每個 CTE 定義，有效地將它們合併到一個語句中。

語法

以下是多個公共表表達式 (CTE) 的基本語法：

WITH
   CTE_NAME1 (column_name) AS (query),
   CTE_NAME2 (column_name) AS (query)
SELECT * FROM CTE_NAME1
UNION ALL
SELECT * FROM CTE_NAME2;

我們可以將多個公共表表達式 (CTE) 與各種 SQL 操作一起使用，例如 UNION、UNION ALL、JOIN、INTERSECT 或 EXCEPT。

示例

在這裡，我們定義了兩個 CTE，即 'CUSTOMERS_IN_DELHI' 和 'CUSTOMERS_IN_MUMBAI'，以根據他們在德里和孟買的地址來區分客戶。然後，我們使用 UNION ALL 運算子將來自這兩個 CTE 的結果組合到單個結果集中，檢索來自這兩個城市的客戶資訊。

WITH
CUSTOMERS_IN_DELHI AS (
   SELECT * FROM CUSTOMERS WHERE ADDRESS = 'Delhi'),
CUSTOMERS_IN_MUMBAI AS (
   SELECT * FROM CUSTOMERS WHERE ADDRESS = 'Mumbai')
SELECT ID, NAME, ADDRESS FROM CUSTOMERS_IN_DELHI
UNION ALL
SELECT ID, NAME, ADDRESS FROM CUSTOMERS_IN_MUMBAI;

輸出

上述查詢的輸出如下所示：

遞迴 CTE

公共表表達式是一個查詢，它會反覆迴圈引用自己的結果，直到返回空結果。

遞迴查詢在其執行期間不斷迭代資料的一個子集，並以自引用方式定義自身。這種自引用機制允許它反覆處理和擴充套件其結果，直到滿足停止條件。

要使 CTE 成為遞迴的，它必須包含 UNION ALL 語句並提供查詢的第二個定義，該定義利用 CTE 本身。這允許 CTE 反覆引用其自身的結果，從而在查詢中建立遞迴行為。

示例

現在，我們使用一個名為 recursive_cust 的遞迴 CTE 來從上面建立的 'CUSTOMERS' 表中檢索資料。最初，我們選擇工資超過 3000 的客戶，然後使用 UNION ALL 運算子遞迴地將年齡超過 25 的客戶追加到結果集中：

WITH recursive_cust (ID, NAME, ADDRESS, AGE) AS (
   SELECT ID, NAME, ADDRESS, AGE
   FROM CUSTOMERS
   WHERE SALARY > 3000
   UNION ALL
   SELECT ID, NAME, ADDRESS, AGE
   FROM CUSTOMERS
   WHERE AGE > 25
)
SELECT * FROM recursive_cust;

輸出

執行上述查詢時，將遞迴顯示來自客戶表中年齡大於 25 或工資大於 3000 的所有資料，如下所示：

示例

在以下查詢中，我們使用一個名為 Numbers 的遞迴 CTE 來生成並顯示從 1 到 5 的數字。遞迴部分不斷將 1 新增到前一個值，直到達到 5，從而建立一個序列：

WITH RECURSIVE Numbers AS (
  SELECT 1 AS N
  UNION ALL
  SELECT N + 1 FROM Numbers WHERE N < 5
)
SELECT n FROM Numbers;

輸出

執行上述查詢後，我們得到以下輸出：

CTE 的優勢

以下是 CTE 的優點：

• CTE 使程式碼維護更容易。

• 它提高了程式碼的可讀性。

• 它提高了查詢的效能。

• CTE 允許簡單地實現遞迴查詢。

CTE 的劣勢

以下是 CTE 的缺點：

• CTE 只能被遞迴成員引用一次。

• 我們不能在儲存過程中使用表變數和 CTE 作為引數。

• CTE 可以代替檢視使用，但 CTE 無法巢狀，而檢視可以。