MySQL遞回CTE(公共表表示式)

在本教學中，您將了解MySQL遞回CTE(公共表表示式)以及如何使用它來遍歷分層資料。

自MySQL 8.0版以來簡要介紹了公共表表示式或叫CTE的功能，因此需要您在計算機上安裝MySQL 8.0，以便在本教學中練習本語句。

1. MySQL遞回CTE簡介

遞回公用表表示式(CTE)是一個具有參照CTE名稱本身的子查詢的CTE。以下說明遞回CTE的語法 -

WITH RECURSIVE cte_name AS (
    initial_query  -- anchor member
    UNION ALL
    recursive_query -- recursive member that references to the CTE name
)
SELECT * FROM cte_name;

遞回CTE由三個主要部分組成：

形成CTE結構的基本結果集的初始查詢(initial_query)，初始查詢部分被稱為錨成員。
遞回查詢部分是參照CTE名稱的查詢，因此稱為遞回成員。遞回成員由一個UNION ALL或UNION DISTINCT運算子與錨成員相連。
終止條件是當遞回成員沒有返回任何行時，確保遞回停止。

遞回CTE的執行順序如下：

首先，將成員分為兩個：錨點和遞回成員。
接下來，執行錨成員形成基本結果集(R0)，並使用該基本結果集進行下一次疊代。
然後，將Ri結果集作為輸入執行遞回成員，並將Ri+1作為輸出。
之後，重複第三步，直到遞回成員返回一個空結果集，換句話說，滿足終止條件。
最後，使用UNION ALL運算子將結果集從R0到Rn組合。

2. 遞回成員限制

遞回成員不能包含以下結構：

聚合函式，如MAX，MIN，SUM，AVG，COUNT等
GROUP BY子句
ORDER BY子句
LIMIT子句
DISTINCT

請注意，上述約束不適用於錨定成員。另外，只有在使用UNION運算子時，要禁止DISTINCT才適用。如果使用UNION DISTINCT運算子，則允許使用DISTINCT。

另外，遞回成員只能在其子句中參照CTE名稱，而不是參照任何子查詢。

3. 簡單的MySQL遞回CTE範例

請參閱以下簡單的遞迴CTE 範例：

WITH RECURSIVE cte_count (n) 
AS (
      SELECT 1
      UNION ALL
      SELECT n + 1 
      FROM cte_count 
      WHERE n < 3
    )
SELECT n 
FROM cte_count;

在此範例中，以下查詢：

SELECT 1

是作為基本結果集返回1的錨成員。

以下查詢 -

SELECT n + 1
FROM cte_count 
WHERE n < 3

是遞迴成員，因為它參照了cte_count的CTE名稱。

遞回成員中的表示式<3是終止條件。當n等於3，遞回成員將返回一個空集合，將停止遞回。

下圖顯示了上述CTE的元素：

遞回CTE返回以下輸出：

遞回CTE的執行步驟如下：

首先，分離錨和遞回成員。
接下來，錨定成員形成初始行(SELECT 1)，因此第一次疊代在n = 1時產生1 + 1 = 2。
然後，第二次疊代對第一次疊代的輸出(2)進行操作，並且在n = 2時產生2 + 1 = 3。

之後，在第三次操作(n = 3)之前，滿足終止條件(n <3)，因此查詢停止。
最後，使用UNION ALL運算子組合所有結果集1,2和3。

4. 使用MySQL遞回CTE遍歷分層資料

我們將使用範例資料庫(yiibaidb)中的employees表進行演示。

mysql> desc employees;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| employeeNumber | int(11)      | NO   | PRI | NULL    |       |
| lastName       | varchar(50)  | NO   |     | NULL    |       |
| firstName      | varchar(50)  | NO   |     | NULL    |       |
| extension      | varchar(10)  | NO   |     | NULL    |       |
| email          | varchar(100) | NO   |     | NULL    |       |
| officeCode     | varchar(10)  | NO   | MUL | NULL    |       |
| reportsTo      | int(11)      | YES  | MUL | NULL    |       |
| jobTitle       | varchar(50)  | NO   |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+
8 rows in set

employees表具有參照employeeNumber欄位的reportsTo欄位。 reportsTo列儲存經理的ID。總經理不會向公司的組織結構中的任何人報告，因此reportsTo列中的值為NULL。

您可以應用遞迴CTE以自頂向下的方式查詢整個組織結構，如下所示：

WITH RECURSIVE employee_paths AS
  ( SELECT employeeNumber,
           reportsTo managerNumber,
           officeCode, 
           1 lvl
   FROM employees
   WHERE reportsTo IS NULL
     UNION ALL
     SELECT e.employeeNumber,
            e.reportsTo,
            e.officeCode,
            lvl+1
     FROM employees e
     INNER JOIN employee_paths ep ON ep.employeeNumber = e.reportsTo )
SELECT employeeNumber,
       managerNumber,
       lvl,
       city
FROM employee_paths ep
INNER JOIN offices o USING (officeCode)
ORDER BY lvl, city;

讓我們將查詢分解成更小的部分，使其更容易理解。
首先，使用以下查詢形成錨成員：

SELECT 
    employeeNumber, reportsTo managerNumber, officeCode
FROM
    employees
WHERE
    reportsTo IS NULL

此查詢(錨成員)返回reportTo為NULL的總經理。

其次，通過參照CTE名稱來執行遞回成員，在這個範例中為 employee_paths：

SELECT 
    e.employeeNumber, e.reportsTo, e.officeCode
FROM
    employees e
        INNER JOIN
    employee_paths ep ON ep.employeeNumber = e.reportsTo

此查詢(遞回成員)返回經理的所有直接上級，直到沒有更多的直接上級。如果遞回成員不返回直接上級，則遞回停止。

第三，使用employee_paths的查詢將CTE返回的結果集與offices表結合起來，以得到最終結果集合。

以下是查詢的輸出：

在本教學中，您已經了解了MySQL遞回CTE以及如何使用它來遍歷分層資料。