认识 CTE: WITH

在编程里，你可以把一段代码单独拿出来起个名字——就像写函数。CTE 也是一样。你可以把 SELECT 子查询单独拎出来，给它起个名字，然后在主 SQL 查询里用它。

CTE（Common Table Expressions，通用表表达式）就像是被嵌套查询折磨疯了的开发者的一口新鲜空气。它们让 SQL 代码不仅更容易懂，还特别优雅。如果你以前被一堆嵌套子查询搞得头晕眼花——现在是时候来感受一下 CTE 的“魔法”了。

想象你在盖房子。一般都想赶紧装窗户、安门（也就是直接写嵌套查询），哪怕墙还没砌好。但用 CTE 就不一样：你先画个整齐的草图——建个临时表，就像先规划房子的布局。然后一步一步把查询的楼层搭起来。又酷又稳又有技术范儿。

本质上，CTE 就是你用 SELECT 查询临时造出来的虚拟表。跟子查询有点像，但更牛逼。 如果在编程里你能把一段逻辑单独写成有名字的函数，那在 SQL 里，这活儿就是 CTE 干的。你写个 SELECT，给它起个名字——后面就能像用大查询的一部分一样用它。是不是很帅？当然啦。

SQL 子查询的例子：

-- 主查询
SELECT *
FROM (
    SELECT *
    FROM students
    WHERE grade > 75
) AS filtered_students; -- 子查询，起了别名 filtered_students

把子查询单独拎出来：

-- CTE/子查询，起了别名 filtered_students
WITH filtered_students AS (
    SELECT *
    FROM students
    WHERE grade > 75
)

-- 主查询
SELECT *
FROM filtered_students;

神奇的是，子查询比 CTE 早了 20 年！SQL-89 标准里就有子查询，CTE 是到 SQL-2009 标准才出现的。

WITH 语法

CTE 以关键字 WITH 开头，大概长这样：

WITH cte_name AS (
    SELECT ... -- 你的查询写这里
)

SELECT ...
FROM cte_name;

这里：

• cte_name —— 你 CTE 的名字。可以随便起个有意义的，比如 high_scores、filtered_data 或 best_students。
• 括号 () 里写的是准备后面要用的数据的查询。
• 定义好 CTE 后，你就能像用普通表一样在主查询里用它。

例子 1：简单 CTE

来看看 CTE 实战怎么用。假设我们有张 students 表——学生名单和他们的分数：

我们的目标——选出所有分数大于 85 的学生，把他们的信息查出来。

不用 CTE 的写法：

可以用子查询搞定：

SELECT *
FROM (
    SELECT *
    FROM students
    WHERE grade > 85
) AS filtered_students;

用 CTE 写法——看着就舒服多了：

WITH filtered_students AS (
    SELECT *
    FROM students
    WHERE grade > 85
)
SELECT *
FROM filtered_students;

是不是感觉更清爽易懂？我们把数据准备（WITH）和主查询（SELECT）分开了。就像先把桌子收拾干净再开始干活——呼吸都顺畅了。

例子 2：多个 CTE

你可以在一个查询里定义多个 CTE。特别适合需要分步骤准备数据的时候。

已知：grades 表，存着学生各门课的分数：

任务：算出每个学生的平均分，再选出平均分大于 85 的学生。

用多个 CTE 的解法：

WITH student_averages AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
),
high_achievers AS (
    SELECT student_id, avg_grade
    FROM student_averages 	-- 用第一个 CTE - student_averages
    WHERE avg_grade > 85
)

SELECT *
FROM high_achievers; -- 用第二个 CTE - high_achievers

这里：

1. student_averages 先准备好每个学生的平均分。
2. high_achievers 用这些数据，选出分数大于 85 的学生。

CTE 和子查询的区别

剧透一下：CTE 不是用来替代子查询的，但有些场景下它们更好用。

子查询就是查询里的查询。要是只用一次还行，但多了代码就乱成一锅粥。

例子：

SELECT *
FROM (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
) AS student_averages
WHERE avg_grade > 85;

子查询可以写在 SELECT、FROM、WHERE、HAVING 里，还能引用外层查询的列。CTE 在这方面就有点局限。

而 CTE 能让代码更易读，维护起来也简单，出错概率低。你不用一层套一层地嵌套查询，只要给子查询结果起个名字，后面直接用就行了。

WITH student_averages AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
)

SELECT *
FROM student_averages
WHERE avg_grade > 85;

CTE 特别适合在一个查询里多次用到准备好的数据。

什么时候用 CTE？

• 当你想把复杂查询拆成几个逻辑步骤时。
• 如果你想让查询更易读、好维护。没人想看一堆像意大利面一样的嵌套代码。
• 临时准备只在当前查询里用的数据时。

最后一个例子：课程分析

来把学到的都串起来：

1. 找出平均分高的学生。
2. 查出他们的名字和选的课程。

WITH student_averages AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
),
high_achievers AS (
    SELECT student_id
    FROM student_averages
    WHERE avg_grade > 85
),
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, sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id;

注意整个结构：

1. 先准备好平均分。
2. 再选出优秀学生。
3. 最后把他们和课程关联起来。

现在你已经可以用 CTE 写出漂亮、易读又强大的 SQL 查询啦。

冲吧——去搞你自己的项目！