用CTE让复杂SQL更好读

想象一下，你要写一个很大的SQL查询，要做一堆互相关联的操作。你可以直接把一堆子查询嵌套在一起，但最后的结果就像意大利面代码一样乱。妥妥的SQL迷宫，连自己都能迷路。

CTE就是你的救生圈！CTE能把复杂的查询拆成一个个有名字的小块，每一块都是独立的逻辑部分。这样你的SQL就清晰又好维护。

对比：子查询 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;

是不是很清楚？哪怕你一个月后再看这个SQL，结构也一目了然。

多个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;

这个查询的好处是，每个任务都写成了独立、逻辑清晰的块。想知道哪些学生成绩牛？看CTE high_achievers。想看他们和课程的关系？看student_courses。老师信息？都在teachers。这种写法让维护和改代码都简单多了。

复杂计算分步骤写

有时候你的查询里有复杂的计算或筛选。别硬塞进一个超长的SQL，拆成几个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再强大也有坑。

数据物化太多。 在PostgreSQL里，CTE默认是“物化”的，也就是结果会先算出来临时存着。如果数据量大，这会拖慢速度。避免这种情况，可以用索引，尽量只选需要的列。

连接写错。 有时候多个CTE的复杂查询不好优化。一定要用EXPLAIN或EXPLAIN ANALYZE检查你的SQL。

CTE用太多。 如果你的CTE又长又乱，可能说明你该把查询拆成几个独立操作了。