SQL語法 - CTE 一般資料表運算式（COMMON TABLE EXPRESSION）

※ CTE是什麼？

在 SQL 中，我們經常需要從多個表中提取數據，因此會使用子查詢 (subquery)。為了讓子查詢更易讀並實現遞迴查詢，我們可以使用 Common Table Expression (CTE)。

CTE 是一個「暫存」且「具名」的結果集合，透過 AS 關鍵字將查詢結果暫時儲存。CTE 的主要用途有兩個：

1. 簡化子查詢：讓查詢更易讀和維護。
2. 遞迴查詢：用於處理層次結構數據或需要遞迴的情況。

簡單來說，CTE 幫助我們將複雜的查詢分解成更易於理解的部分，並且可以用來進行遞迴查詢，使得 SQL 查詢更簡潔和高效。

※ 語法

WITH cte_name AS (
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT column1, column2
FROM cte_name
WHERE another_condition;

語法解析：

1. 定義 CTE：
2. • 使用 WITH cte_name AS 開頭，給 CTE 一個名字（這裡是 cte_name）。
   • 在括號內寫一個查詢，這個查詢會生成一個臨時的結果集。
2. 使用 CTE：：

• 在主查詢中，使用剛剛定義的 CTE 名字（cte_name）來引用這個臨時結果集。
• 主查詢可以對這個臨時結果集進行進一步的查詢和篩選。

※ 解析順序

1. 宣告CTEs：首先，你會宣告一個或多個CTEs，每個CTE包含一個查詢語句（例如SELECT、INSERT、UPDATE、DELETE、CREATE等）。
2. 使用CTEs：在宣告的CTE之後，你可以在後續的CTE或最終的查詢語句中使用這些CTEs。
3. 依序解析：解析順序是依照CTE宣告的順序進行的。也就是說，第一個CTE會先被解析，然後是第二個，依此類推。
4. 最終查詢：在所有CTE宣告完成後，你可以撰寫最終的查詢語句（例如SELECT、INSERT、UPDATE、DELETE、CREATE等），並且可以在這些查詢中使用之前宣告的所有CTEs。

這樣的解析順序確保了每個CTE都可以在後續的CTE或最終查詢中被引用和使用。

※ 實例- 找到最多歌單的使⽤者資訊

-- 宣告CTE來計算每個使用者的歌單數量
WITH UserPlaylistCount AS (
    SELECT user_id, COUNT(*) AS playlist_count
    FROM playlists
    GROUP BY user_id
)

-- 使用CTE來找到擁有最多歌單的使用者
SELECT u.*
FROM users u
JOIN UserPlaylistCount upc ON u.id = upc.user_id
WHERE upc.playlist_count = (
    SELECT MAX(playlist_count)
    FROM UserPlaylistCount
);

解析順序如下：

1. 宣告CTE：UserPlaylistCount 計算每個使用者的歌單數量。

• SELECT user_id, COUNT(*) AS playlist_count：選擇 user_id 並計算每個 user_id 對應的歌單數量，並將這個計數命名為 playlist_count。
• FROM playlists：從 playlists 表格中檢索數據。
• GROUP BY user_id：根據 user_id 進行分組，以計算每個使用者的歌單數量。

2. 使用CTE來找到擁有最多歌單的使用者。

• SELECT u.*：選擇 users 表格中的所有欄位。
• FROM users u：從 users 表格中檢索數據，並將其命名為 u。
• JOIN UserPlaylistCount upc ON u.id = upc.user_id：將 users 表格與 UserPlaylistCount CTE 進行連接，條件是 users 表格中的 id 與 UserPlaylistCount CTE 中的 user_id 相等。
• WHERE upc.playlist_count = (SELECT MAX(playlist_count) FROM UserPlaylistCount)：篩選出擁有最多歌單的使用者。這裡使用了一個子查詢來找到 UserPlaylistCount CTE 中的最大 playlist_count，並將其與 upc.playlist_count 進行比較。

總結

這段SQL語句的目的是找到擁有最多歌單的使用者。首先，使用CTE計算每個使用者的歌單數量，然後在最終查詢中使用這個CTE來找到擁有最多歌單的使用者。

※ 與 Subquery 的比較

CTE（Common Table Expressions）

優點：

1. 可讀性高：CTE可以將複雜的查詢分解成多個部分，使查詢更易於理解和維護。
2. 子任務的邏輯清楚地分開：CTE允許你將子任務查詢邏輯分成多個部分，這樣在解決問題時，可以更清晰地思考每個步驟。
3. 減少重複：CTE可以在同一查詢中多次引用，避免重複代碼。
4. 遞迴查詢：CTE支持遞迴查詢，這在處理層次結構數據時非常有用。

缺點：

1. 性能：在某些情況下，CTE可能會比子查詢慢，特別是當CTE被多次引用時。
2. 範圍限制：CTE的作用範圍僅限於它所屬的查詢塊，不能在其他查詢中重用。

子查詢（Subqueries）

優點：

1. 靈活性：子查詢可以嵌套在SELECT、INSERT、UPDATE、DELETE等語句中，提供了很大的靈活性。
2. 性能：在某些情況下，子查詢可能比CTE更快，特別是當子查詢只執行一次時。

缺點：

1. 可讀性差：嵌套的子查詢可能會使查詢變得複雜且難以理解。
2. 重用性差：子查詢通常不能在同一查詢中多次引用，這可能導致代碼重複。
3. 調試困難：由於子查詢嵌套在主查詢中，調試和維護可能會變得更加困難。

總結

• CTE 更適合用於需要高可讀性和邏輯分離的複雜查詢。
• 子查詢 更適合用於需要靈活性和可能具有性能優勢的簡單查詢。