用 WITH 創建臨時資料表

臨時資料表超適合拿來存中間結果，之後還能繼續操作。就像寫程式時，不用一直複製貼上同一大段 code，直接存進變數，想用就用。在 SQL 的世界裡，這種「變數」的角色常常就是臨時資料表。

不過 CTE 讓這一切更簡單——它讓你不用多餘的步驟就能搞定臨時資料表：

• 不用擔心刪除。

  CTE 只在查詢執行時存在。SQL 執行完，CTE 就像隱形小助手一樣自動消失，完全不留痕跡。

• 程式碼更清楚。

  用 CTE 比自己手動創建、刪除實體臨時資料表簡單多了。所有東西都一目了然，超直觀。

• 超適合「一次性」任務。

  如果你只是想在某個步驟處理資料——CTE 超級適合。快、乾淨，沒副作用。

CTE 當臨時資料表的語法

先來複習一下 CTE 的語法：

WITH cte_name AS (
    SELECT ...
)
SELECT ...
FROM cte_name;

這裡 WITH 會創建一個臨時的「資料表表達式」，在這個查詢裡都能用。它看起來像資料表，但只活到查詢結束。

範例：找出優秀學生

我們來用 CTE 創建一個臨時資料表，找出平均成績（grade）大於 90 的學生，然後把他們列出來。

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
)
SELECT *
FROM high_achievers;

這裡我們：

• 用 WITH 創建臨時資料表 high_achievers。
• 在 CTE 裡把每個學生（student_id）的成績（grades）分組，算出平均分數。
• 在 SELECT * 查詢裡直接把 high_achievers 當一般資料表用。

CTE 跟臨時資料表的比較

有時候會想問：用 CTE 跟用 CREATE TEMP TABLE 創建的臨時資料表有什麼差別？

這裡有個傳統臨時資料表（CREATE TEMP TABLE）的範例，用來處理同樣的資料：

CREATE TEMP TABLE high_achievers_temp AS
	SELECT student_id, AVG(grade) AS avg_grade
	FROM grades
	GROUP BY student_id
	HAVING AVG(grade) > 90;

SELECT *
FROM high_achievers_temp;

DROP TABLE high_achievers_temp;  -- 記得刪掉資料表喔！

然後這是用 CTE 寫的同樣查詢：

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
)
SELECT *
FROM high_achievers;

什麼時候該用 CTE，什麼時候該用臨時資料表

CTE 超適合你想快速準備中間資料、馬上用掉——完全不用多餘的步驟。你不用擔心刪除：CTE 查詢結束就自動消失。它直接寫在 SQL 結構裡，不像臨時資料表藏在外面。這讓查詢更乾淨、簡單又好懂，特別是你要連續做好幾步資料處理時。而且 CTE 還能組合、巢狀，寫出很複雜的邏輯——這我們之後會再聊。

這種結構很適合一次性的查詢，資料只在這個查詢裡用。如果你打算在系統不同地方多次用到結果，或想讓中間資料在整個 session 裡都能用——臨時資料表會更穩。尤其資料量很大、需要高效能時：這時實體臨時資料表會更穩定、速度也更快。

一切看需求：CTE 是快速、優雅的本地資料處理工具。臨時資料表則是複雜、長時間任務的好幫手。

範例：資料彙總

假設我們有一個 enrollments 資料表，記錄哪些學生選了哪些課。我們想知道每門課有多少學生，而且只看學生超過 5 人的課。

用 CTE 寫法如下：

WITH course_counts AS (
    SELECT course_id, COUNT(student_id) AS student_count
    FROM enrollments
    GROUP BY course_id
    HAVING COUNT(student_id) > 5
)

SELECT *
FROM course_counts
ORDER BY student_count DESC;

多個 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
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
)

SELECT ha.student_id, ha.avg_grade, sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id;

現在查詢裡有兩個 CTE：

1. high_achievers 找出優秀學生。
2. student_courses 把學生跟課程對起來。

結果就是平均分高的學生和他們的課程清單。

用 CTE 常見錯誤

資料量太大。 CTE 會在 PostgreSQL 記憶體裡 materialize。如果你 CTE 結果超大，查詢會變慢，甚至爆記憶體。

濫用。 有時候用 CTE 反而比直接寫子查詢還複雜，會讓人看不懂。

忘了加索引。 如果 CTE 裡的資料來自沒加索引的大表，查詢會變慢。