用 CTE 讓複雜查詢更好讀

想像一下，你要寫一個超大的 SQL 查詢，要一次做很多彼此有關的操作。你可以直接把一堆子查詢塞在一起，但結果會像義大利麵一樣亂七八糟。根本就是 SQL 迷宮，連自己都會迷路 XD。

CTE 就是你的救生圈啦！CTE 讓你把複雜查詢拆成一段段有名字的區塊，每一段都很有邏輯。這樣你的查詢就超清楚、超好維護。

比較一下：子查詢 vs CTE

乍看之下兩種寫法都一樣——都是根據課程過濾成績，然後算每個學生的平均分。但你仔細看：子查詢的邏輯都「藏」在括號裡，CTE 則是直接拉出來，還有個很明顯的名字 filtered_grades。想像一下，如果中間步驟不是兩個，而是十個呢！

子查詢：

SELECT student_id, AVG(grade) AS avg_grade
FROM (
    SELECT student_id, grade
    FROM grades
    WHERE course_id = 101
) subquery
GROUP BY student_id;

CTE：

WITH filtered_grades AS (
    SELECT student_id, grade
    FROM grades
    WHERE course_id = 101
)
SELECT student_id, AVG(grade) AS avg_grade
FROM filtered_grades
GROUP BY student_id;

找找看有哪 10 個不同。當然，CTE 真的超好讀！

用 CTE 把複雜查詢分成步驟

CTE 讓你可以一步一步寫查詢，每個階段的結果都很清楚。比如說，你想要拿到學生的課程平均分，還要加上他們的老師資料，就可以把這個需求拆成幾個部分。

範例：

WITH avg_grades AS (
    SELECT student_id, course_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id, course_id
),
course_teachers AS (
    SELECT course_id, teacher_id
    FROM courses
)

SELECT ag.student_id, ag.avg_grade, ct.teacher_id
FROM avg_grades ag
JOIN course_teachers ct ON ag.course_id = ct.course_id;

是不是超好讀？就算你一個月後回來看這個查詢，結構還是很明顯。

多個 CTE 寫大型報表

來看一個更複雜的報表例子。假設我們有一個大學資料庫，要做一份最厲害學生、他們的課程和老師的報表。計畫如下：

1. 先找出平均分很高（超過 90 分）的學生。
2. 再把他們跟課程對起來。
3. 最後加上老師的資料。

多個 CTE 的查詢：

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
),
student_courses AS (
    SELECT e.student_id, c.course_name, c.teacher_id
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
),
teachers AS (
    SELECT teacher_id, name AS teacher_name
    FROM teachers
)

SELECT ha.student_id, ha.avg_grade, sc.course_name, t.teacher_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id
JOIN teachers t ON sc.teacher_id = t.teacher_id;

這個查詢的好處是，每個任務都拆成一個獨立又有邏輯的區塊。想知道哪些學生超強？看 high_achievers。想看他們跟課程的關係？看 student_courses。老師資料？都在 teachers。這種寫法超級好維護、也方便改。

把複雜計算分成步驟

有時候你的查詢會有很複雜的計算或過濾。不要硬把全部塞進一個超長查詢，直接拆成幾個 CTE 就好。

範例：

WITH course_stats AS (
    SELECT course_id, COUNT(student_id) AS student_count, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY course_id
),
popular_courses AS (
    SELECT course_id
    FROM course_stats
    WHERE student_count > 50
)
SELECT c.course_name, cs.student_count, cs.avg_grade
FROM popular_courses pc
JOIN course_stats cs ON pc.course_id = cs.course_id
JOIN courses c ON c.course_id = pc.course_id;

這裡我們先在 course_stats 收集課程的統計，再在 popular_courses 過濾出熱門課程，最後跟課程表合併。這種方法可以把中間步驟都拆出來，查詢邏輯一目了然。

什麼時候 CTE 超級好用？

這裡有幾種 CTE 特別有用的情境：

1. 分析和報表。比如說要算很複雜的指標，還要分組過濾。
2. 處理階層結構。用遞迴 CTE 來做分類樹或組織結構。
3. 重複用到的資料。像是同一個查詢結果要在不同階段用到。

用 CTE 常見的錯誤

當然，CTE 這種強大的工具也有一些小陷阱。

資料過度 materialize。 在 PostgreSQL 裡，CTE 預設會「materialize」，也就是結果會先算好暫存。如果資料量很大，這會拖慢查詢速度。避免這種情況可以用 index，還有只選你真的需要的欄位。

連接寫錯。 有時候多個 CTE 的複雜查詢會很難優化。記得用 EXPLAIN 或 EXPLAIN ANALYZE 來檢查你的查詢。

CTE 用太多。 如果你的 CTE 變得又長又亂，可能要考慮把查詢拆成幾個獨立操作。