在 SQL 中,我們經常需要從多個表中提取數據,因此會使用子查詢 (subquery)。為了讓子查詢更易讀並實現遞迴查詢,我們可以使用 Common Table Expression (CTE)。
CTE 是一個「暫存」且「具名」的結果集合,透過 AS
關鍵字將查詢結果暫時儲存。CTE 的主要用途有兩個:
簡單來說,CTE 幫助我們將複雜的查詢分解成更易於理解的部分,並且可以用來進行遞迴查詢,使得 SQL 查詢更簡潔和高效。
WITH cte_name AS (
SELECT column1, column2
FROM table_name
WHERE condition
)
SELECT column1, column2
FROM cte_name
WHERE another_condition;
使用 CTE:
:這樣的解析順序確保了每個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
);
解析順序如下:
UserPlaylistCount
計算每個使用者的歌單數量。user_id
並計算每個 user_id
對應的歌單數量,並將這個計數命名為 playlist_count
。playlists
表格中檢索數據。user_id
進行分組,以計算每個使用者的歌單數量。users
表格中的所有欄位。users
表格中檢索數據,並將其命名為 u
。users
表格與 UserPlaylistCount
CTE 進行連接,條件是 users
表格中的 id
與 UserPlaylistCount
CTE 中的 user_id
相等。UserPlaylistCount
CTE 中的最大 playlist_count
,並將其與 upc.playlist_count
進行比較。這段SQL語句的目的是找到擁有最多歌單的使用者。首先,使用CTE計算每個使用者的歌單數量,然後在最終查詢中使用這個CTE來找到擁有最多歌單的使用者。
CTE(Common Table Expressions)
優點:
缺點:
優點:
缺點: