SQL - 公共表表达式 (CTE)

你好，未來的 SQL 巔頂大師！今天，我們將踏上一段令人振奮的旅程，進入公共表表达式（CTE）的世界，簡稱為 CTE。如果你是編程新手，別擔心——我會一步一步地引導你，就像我這些年來對無數學生所做的那樣。所以，拿起你喜歡的飲料，放鬆一下，我們來深入探究吧！

SQL 公共表表达式

想像你正在籌備一個大型派對（因為誰不喜歡一個好的比喻呢？）。你有一個任務清單，並且想要把它們分解成更小、更易於管理的部分。這就是 SQL 中的公共表表达式所做的——它幫助我們將複雜的查詢分解成更簡單、更易於閱讀的部分。

CTE 就像一個臨時命名的結果集，你可以在 SELECT、INSERT、UPDATE、DELETE 或 MERGE 語句中引用它。它定義在單個語句的執行範圍內。把它當作為你的查詢創建一個臨時表。

讓我們看一個簡單的例子：

WITH cte_example AS (
SELECT 'Hello, CTE!' AS greeting
)
SELECT greeting FROM cte_example;

在這個例子中：

1. 我們從 WITH 關鍵字開始，這標誌著我們 CTE 的開始。
2. 我們給 CTE 命名：cte_example。
3. 我們定義 CTE 將包含的内容：在這個例子中，一個簡單的 SELECT 語句，創建一個名為 'greeting' 的列，值為 'Hello, CTE!'。
4. 在 CTE 定義之後，我們有我們的主要查詢，它使用 CTE。

當你運行這個查詢時，你會看到：

| greeting    |
|-------------|
| Hello, CTE! |

這不是很棒嗎？我們剛創建了我們的第一個 CTE！

MySQL 的 WITH 語句

現在，讓我們來討論 MySQL 中的 WITH 語句。它是讓我們的 CTE 起作用的魔法棒。一般語法看起來像這樣：

WITH cte_name [(column_list)] AS (query)
SELECT * FROM cte_name;

這裡有一個更實用的例子。假設我們有一個員工表：

CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
department VARCHAR(50),
salary DECIMAL(10, 2)
);

INSERT INTO employees VALUES
(1, 'Alice', 'HR', 50000),
(2, 'Bob', 'IT', 60000),
(3, 'Charlie', 'Finance', 55000),
(4, 'David', 'IT', 65000),
(5, 'Eve', 'HR', 52000);

現在，讓我們使用 CTE 來找出各個部門的平均薪水：

WITH dept_avg_salary AS (
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
)
SELECT * FROM dept_avg_salary
ORDER BY avg_salary DESC;

這個查詢將給我們：

| department | avg_salary |
|------------|------------|
| IT         | 62500.00   |
| Finance    | 55000.00   |
| HR         | 51000.00   |

在這裡，我們使用 CTE 計算每個部門的平均薪水，然後從這個 CTE 選擇以顯示結果。就像我們創建了一個包含平均薪水的臨時表，然後在我們的主要查詢中使用它。

從多個表中使用 CTE

CTE 不僅限於一個表。我們可以在 CTE 定義中使用多個表，就像在常规查詢中一樣。讓我們在我們的例子中添加一個部門表：

CREATE TABLE departments (
id INT PRIMARY KEY,
name VARCHAR(50),
location VARCHAR(50)
);

INSERT INTO departments VALUES
(1, 'HR', 'New York'),
(2, 'IT', 'San Francisco'),
(3, 'Finance', 'Chicago');

現在，讓我們使用 CTE 來組合兩個表的信息：

WITH dept_info AS (
SELECT e.department,
AVG(e.salary) AS avg_salary,
d.location
FROM employees e
JOIN departments d ON e.department = d.name
GROUP BY e.department, d.location
)
SELECT * FROM dept_info
ORDER BY avg_salary DESC;

這將給我們：

| department | avg_salary | location      |
|------------|------------|---------------|
| IT         | 62500.00   | San Francisco |
| Finance    | 55000.00   | Chicago       |
| HR         | 51000.00   | New York      |

在這個例子中，我們的 CTE 連接了員工和部門表，計算平均薪水，並包括位置信息。

遞歸 CTE

現在，讓我們來看看真正有趣的部分——遞歸 CTE！這些就像是 SQL 世界中的俄羅斯套娃。一個遞歸 CTE 指向自己，讓你可以對層次結構或樹狀結構數據進行操作。

讓我們創建一個簡單的員工層次結構例子：

CREATE TABLE employee_hierarchy (
id INT PRIMARY KEY,
name VARCHAR(50),
manager_id INT
);

INSERT INTO employee_hierarchy VALUES
(1, 'Big Boss', NULL),
(2, 'Manager A', 1),
(3, 'Manager B', 1),
(4, 'Employee 1', 2),
(5, 'Employee 2', 2),
(6, 'Employee 3', 3);

現在，讓我們使用遞歸 CTE 來顯示整個層次結構：

WITH RECURSIVE emp_hierarchy AS (
SELECT id, name, manager_id, 0 AS level
FROM employee_hierarchy
WHERE manager_id IS NULL

UNION ALL

SELECT e.id, e.name, e.manager_id, eh.level + 1
FROM employee_hierarchy e
JOIN emp_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM emp_hierarchy
ORDER BY level, id;

這個查詢將產生：

| id | name       | manager_id | level |
|----|------------|------------|-------|
| 1  | Big Boss   | NULL       | 0     |
| 2  | Manager A  | 1          | 1     |
| 3  | Manager B  | 1          | 1     |
| 4  | Employee 1 | 2          | 2     |
| 5  | Employee 2 | 2          | 2     |
| 6  | Employee 3 | 3          | 2     |

這個遞歸 CTE 從頂級員工（Big Boss）開始，然後遞歸地找到每個經理下的所有員工。

CTE 的優點

CTE 有以下幾個優點：

優點	描述
可讀性	CTE 使複雜查詢更易於閱讀，通過將它們分解為命名的子查詢。
可重用性	你可以在查詢中多次引用一個 CTE。
遞歸	CTE 允許你寫遞歸查詢，這對於層次數據非常有用。
簡化	它們可以簡化複雜的連接和子查詢。
維護性	CTE 使查詢更易於維護和修改。

CTE 的缺點

儘管 CTE 非常強大，但它們也有一些限制：

缺點	描述
性能	在某些情況下，CTE 的性能可能不如派生表或視圖。
範圍	CTE 只在定義它的單個語句範圍內有效。
複雜性	對於非常簡單的查詢，使用 CTE 可能會增加不必要的複雜性。
數據庫支持	不是所有的數據庫系統都支持 CTE，儘管大多數現代的數據庫系統都支持。

這就是 CTE 的所有內容，各位！我們從基本概念到遞歸查詢，一路探索了公共表表達式的世界。記住，像學習任何新技能一樣，精通 CTE 需要練習。所以不要害怕在你的查詢中嘗試它們。在你意識到之前，你將能夠像專業人士一樣寫作 CTE，讓你的數據庫查詢更乾淨、更高效。快樂查詢！

Credits: Image by storyset