用 WITH 创建临时表

临时表就是个超方便的办法，能把中间结果存下来，后面随便用。就像写代码时，不用一直复制粘贴一大坨代码，直接存变量里，想用就用。在 SQL 里，这种“变量”经常就是临时表。

不过 CTE 让这事儿更简单——它能让你不用折腾就搞定临时表：

• 不用操心删表。

  CTE 只在你执行 SQL 的那一瞬间活着。SQL 跑完，CTE 就消失了，像个隐形小助手，帮你搞定一切然后悄悄溜了。

• 代码更清晰。

  写 CTE 比折腾物理临时表简单多了。啥都在眼前，一目了然。

• 超适合“一次性”任务。

  如果你只是想在某一步处理下数据——CTE 完美搞定。快、干净、没副作用。

CTE 作为临时表的语法

先来复习下 CTE 的语法：

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

这里 WITH 创建了一个临时的“表表达式”，在整个 SQL 语句里都能用。它看起来像表，但只活到 SQL 执行完。

例子：找出学霸

咱们用 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 结构里，不像临时表那样藏在外面。这样写出来的 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;

现在 SQL 里有两个 CTE：

1. high_achievers 找出学霸。
2. student_courses 把学生和课程对应起来。

结果就是高分学生和他们的课程列表。

用 CTE 时常见的坑

数据太大。 CTE 在 PostgreSQL 里是放内存里的。如果你 CTE 结果太大，SQL 可能会变慢，甚至爆内存。

滥用。 有些地方用 CTE 还不如普通子查询简单，反而让人晕。

忘了索引。 如果 CTE 里的数据来自大表但没索引，SQL 会很慢。