SQL CTE 公用表表达式

学习WITH · 难度：高级 · +15XP

SQL CTE 公用表表达式

CTE（Common Table Expression，公用表表达式）是一种在 SQL 查询中定义临时命名结果集的方式。它使用 WITH 关键字开头，可以理解为给一个子查询起了一个名字，让你在后续的主查询中多次引用它。CTE 最大的优势是让复杂查询变得清晰易读——你可以将一个大问题拆解为多个有名称的步骤，像搭积木一样逐层构建查询逻辑。

CTE 基本语法

WITH cte名称 AS (
    -- 子查询
    SELECT 列1, 列2 FROM 表名 WHERE 条件
)
SELECT * FROM cte名称;

CTE 的作用范围仅限于紧随其后的一条语句（SELECT/INSERT/UPDATE/DELETE），执行完毕后即被销毁。

基础示例

-- 计算平均分，然后查询高于平均分的学生
WITH avg_scores AS (
    SELECT AVG(score) AS avg_score FROM students
)
SELECT name, score
FROM students, avg_scores
WHERE score > avg_score;
-- 等价于子查询写法（对比可读性）
SELECT name, score FROM students
WHERE score > (SELECT AVG(score) FROM students);

多个CTE链式定义

你可以在一个查询中定义多个 CTE，后面的 CTE 可以引用前面的 CTE：

WITH
-- 第一步：计算每个学生的平均分
student_avg AS (
    SELECT student_id, AVG(score) AS avg_score
    FROM scores GROUP BY student_id
),
-- 第二步：基于上一步筛选优等生
top_students AS (
    SELECT student_id, avg_score
    FROM student_avg WHERE avg_score >= 85
)
-- 第三步：关联学生信息
SELECT s.name, t.avg_score
FROM top_students t
JOIN students s ON s.id = t.student_id;

CTE + 窗口函数

CTE 与窗口函数配合使用是常见的高效查询模式：

WITH ranked AS (
    SELECT name, score,
           ROW_NUMBER() OVER (ORDER BY score DESC) AS rank
    FROM students
)
SELECT name, score, rank FROM ranked WHERE rank <= 3;

CTE vs 子查询 vs 临时表

特性	CTE	子查询	临时表
可读性	高（有名称）	低（嵌套）	中
可复用	同一查询内可多次引用	需重复编写	跨查询可用
存储	不存储（虚拟）	不存储	物理存储
递归支持	支持	不支持	不支持

实战任务

1. 使用 CTE 计算平均分，查询高于平均分的学生
2. 定义两个 CTE 链式查询：先算平均分，再筛选高分
3. 结合 CTE 和 ROW_NUMBER 查询前 3 名