使用MySQL 8中的常見表表達式（CTE）

CTEs 是MySQL 8.0 引入的特性，提升複雜查詢的可讀性與維護性。 1. CTE 是臨時結果集，僅在當前查詢中有效，結構清晰，支持重複引用；2. 相比子查詢，CTE 更易讀、可重用且支持遞歸；3. 遞歸CTE 可處理層級數據，如組織結構，需包含初始查詢與遞歸部分；4. 使用建議包括避免濫用、命名規範、關注性能及調試方法。

CTEs（Common Table Expressions）是MySQL 8.0 引入的一個非常實用的特性，它讓複雜查詢更清晰、可讀性更強。如果你之前寫過嵌套子查詢或者多層派生表的SQL，用上CTE 後會發現結構更清爽，也更容易維護。

什麼是CTE？

CTE 就是一個臨時的結果集，你可以在一個查詢中定義它，並在後續的查詢中像使用普通表一樣引用它。它只在當前查詢執行期間存在，不會持久化到數據庫裡。

舉個簡單的例子：你想查出每個部門工資最高的員工。如果不用CTE，可能得寫多個子查詢嵌套，邏輯繞。而用CTE 的話，可以先把“每個部門最高工資”作為一個中間結果，再關聯員工表來篩選數據。

 WITH MaxSalaries AS (
    SELECT department_id, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.*
FROM employees e
JOIN MaxSalaries ms
ON e.department_id = ms.department_id AND e.salary = ms.max_salary;

這樣看起來是不是更直觀了？而且你還可以在一個查詢裡定義多個CTE，按需組合使用。

CTE 的基本語法

CTE 的結構很明確，開頭是WITH ，然後是你給這個臨時結果集起的名字，接著是括號裡的查詢語句。之後就可以在這個查詢範圍內反複使用這個名字了。

格式如下：

 WITH cte_name [(column_list)] AS (
    -- 查詢語句)
-- 後續使用CTE 的主查詢

幾點注意：

• CTE 可以帶列名，如果不指定的話，默認會從查詢中自動獲取。
• 如果有多個CTE，可以用逗號分隔開，依次定義。
• CTE 只在當前查詢中有效，不能跨查詢復用。

CTE 和子查詢的區別

很多人會問：CTE 跟子查詢有什麼區別？其實它們在功能上很相似，但CTE 在可讀性和重用性方面更有優勢。

比如你要在多個地方都用到“某個月份的銷售匯總”，用子查詢就得寫好幾遍，而用CTE 只要定義一次就能多次調用。

使用遞歸CTE 處理層級數據

MySQL 8.0 還支持遞歸CTE ，非常適合處理樹形結構或層級數據，比如組織架構、分類目錄、評論回复鍊等。

假設你有一個employees表，裡麵包含員工ID 和上級經理ID，你想找出某個領導下面的所有下屬，這時候遞歸CTE 就派上用場了。

示例代碼如下：

 WITH RECURSIVE Subordinates AS (
    SELECT employee_id, manager_id, name
    FROM employees
    WHERE manager_id = 100 -- 初始條件：查找直接下屬UNION ALL

    SELECT e.employee_id, e.manager_id, e.name
    FROM employees e
    INNER JOIN Subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM Subordinates;

這段代碼先選出直屬下級，然後不斷遞歸地找這些人的下屬，直到沒有更多記錄為止。遞歸CTE 的核心就是兩個部分：

• 初始查詢（non-recursive part）
• 遞歸部分（recursive part），通過UNION ALL連接

需要注意的是，遞歸不能無限進行下去，否則會報錯。你可以通過設置cte_max_recursion_depth來控制最大遞歸深度。

實際應用建議

CTE 很強大，但在實際使用時也有一些小技巧和注意事項：

• 別濫用CTE ：不是所有場景都適合用CTE，簡單查詢沒必要拆分成CTE，反而增加複雜度。
• 命名規範：給CTE 起個有意義的名字，比如TopCustomers或RecentOrders ，有助於別人理解你的邏輯。
• 性能問題：CTE 並不一定會提升性能，它更像是邏輯上的封裝。如果對性能敏感，最好結合索引和執行計劃一起分析。
• 調試方法：想看看CTE 中間結果？可以直接把CTE 寫完後SELECT * FROM cte_name ，方便調試。

基本上就這些。 CTE 是現代SQL 中非常實用的一部分，尤其是在寫複雜查詢的時候，能幫你理清思路，提高可讀性。剛開始可能不太習慣，但一旦用上手，你會發現寫SQL 的體驗真的不一樣。

以上是使用MySQL 8中的常見表表達式（CTE）的詳細內容。更多資訊請關注PHP中文網其他相關文章！