2024-08-05|閱讀時間 ‧ 約 7 分鐘

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:
      • 使用 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 更適合用於需要高可讀性和邏輯分離的複雜查詢。
    • 子查詢 更適合用於需要靈活性和可能具有性能優勢的簡單查詢。



    分享至
    成為作者繼續創作的動力吧!
    © 2024 vocus All rights reserved.