SQL公共表表达式（CTE）实战：用高中考试数据学会高效查询

最新推荐文章于 2025-08-23 16:56:09 发布

原创最新推荐文章于 2025-08-23 16:56:09 发布 · 832 阅读

CC 4.0 BY-SA版权

文章标签：

在 SQL 查询中，面对多层嵌套子查询（比如 “先查班级平均分，再查高于平均分的学生，最后统计各科达标率”），代码往往会变得杂乱难懂 —— 缩进嵌套像 “千层饼”，修改时需要逐层定位，可读性和维护性极差。而公共表表达式（CTE，Common Table Expressions） 正是解决这一问题的 “利器”，它能将复杂逻辑拆分为多个独立的 “临时结果集”，让 SQL 代码像 “搭积木” 一样清晰。本文将以高中考试数据为场景，从基础到进阶，带你掌握 CTE 的核心用法。

一、什么是 CTE？一句话看懂核心价值

CTE 是一种临时的、可复用的结果集，它在SELECT、INSERT、UPDATE等语句中定义，仅在当前查询中生效（查询结束后自动消失，不会像表一样持久化）。其核心价值在于：

拆分复杂逻辑：将多步查询拆分为多个独立 CTE，每步只做一件事；
提升可读性：给临时结果集命名（如cte_class_avg表示 “班级平均分”），代码意图一目了然；
支持递归查询：这是 CTE 独有的能力，可用于处理层级数据（如高中年级 - 班级 - 学生的层级关系）。

CTE 的基础语法非常简单，用WITH关键字定义，格式如下：

WITH 临时结果集名称1 AS (

-- 第一步查询逻辑：生成临时结果集1

SELECT 列1, 列2... FROM 表名 WHERE 条件

临时结果集名称2 AS (

-- 第二步查询逻辑：可引用临时结果集1

SELECT 列A, 列B... FROM 临时结果集名称1 WHERE 条件

)

-- 最终查询：引用前面定义的临时结果集

SELECT * FROM 临时结果集名称2;

二、实战场景：用高中考试数据学 CTE

为了让例子更贴近生活，我们先定义 3 张高中考试相关的表，后续所有 CTE 示例都基于这 3 张表展开：

基础数据表结构

students（学生表）：存储学生基本信息

student_id	student_name	class	grade
101	张三	1 班	高一
102	李四	1 班	高一
103	王五	2 班	高一
104	赵六	2 班	高二

courses（课程表）：存储考试科目信息

course_id	course_name
1	数学
2	语文
3	英语

exam_scores（考试成绩表）：存储学生各科成绩

score_id	student_id	course_id	score	exam_date
1	101	1	85	2024-06-10
2	101	2	78	2024-06-10
3	102	1	92	2024-06-10
4	102	2	88	2024-06-10
5	103	1	76	2024-06-10
6	103	3	95	2024-06-10
7	104	2	80	2024-06-10

三、CTE 的 3 类核心用法

3.1 单 CTE：解决 “一步查询” 的简化需求

当查询需要先做一次数据筛选 / 计算，再基于结果做最终查询时，单 CTE 能避免子查询嵌套，让逻辑更清晰。

场景 1：查询 2024 年 6 月考试中，数学成绩高于班级平均分的学生姓名和成绩

如果不用 CTE，需要嵌套子查询（先查数学班级平均分，再查高于平均分的学生）：

-- 无CTE：子查询嵌套，可读性差

SELECT

s.student_name,

e.score

FROM students s

JOIN exam_scores e ON s.student_id = e.student_id

JOIN courses c ON e.course_id = c.course_id

WHERE

c.course_name = '数学'

AND e.exam_date = '2024-06-10'

AND e.score > (

-- 子查询：计算数学班级平均分

SELECT AVG(score)

FROM exam_scores

JOIN courses ON exam_scores.course_id = courses.course_id

WHERE course_name = '数学' AND exam_date = '2024-06-10'

);

用 CTE 改写后，逻辑拆分为两步，一目了然：

-- 单CTE：先算数学平均分，再查高于平均分的学生

WITH cte_math_avg AS (

-- 第一步：计算2024年6月数学考试的班级平均分

SELECT AVG(score) AS math_class_avg

FROM exam_scores e

JOIN courses c ON e.course_id = c.course_id

WHERE c.course_name = '数学' AND e.exam_date = '2024-06-10'

)

-- 第二步：查询高于平均分的学生信息

SELECT

s.student_name,

e.score,

m.math_class_avg -- 可直接引用CTE的平均分

FROM students s

JOIN exam_scores e ON s.student_id = e.student_id

JOIN courses c ON e.course_id = c.course_id

JOIN cte_math_avg m ON 1=1 -- 关联CTE（因CTE只有1行数据，用1=1匹配）

WHERE

c.course_name = '数学'

AND e.exam_date = '2024-06-10'

AND e.score > m.math_class_avg;

结果：

student_name	score	math_class_avg
李四	92	84.33

优势：CTE 将 “计算平均分” 和 “筛选学生” 拆分为两个独立步骤，后续修改时（如改考试日期），只需定位到对应的 CTE，无需在嵌套中找子查询。

3.2 多 CTE：处理 “多步依赖” 的复杂需求

当查询需要多步计算，且后续步骤依赖前序步骤的结果时（比如 “先算各科平均分→再算学生总分→最后查总分前 10 且各科高于平均分的学生”），多 CTE 能像 “流水线” 一样逐步处理。

场景 2：2024 年 6 月考试中，查询高一年级学生中 “总分≥240 分” 且 “各科成绩不低于该科年级平均分” 的学生名单及各科成绩

这个需求需要 3 步计算，用多 CTE 拆解：

WITH

-- 第一步：计算高一年级各科的年级平均分

cte_grade_course_avg AS (

SELECT

c.course_name,

AVG(e.score) AS grade_course_avg -- 高一某科的平均分

FROM exam_scores e

JOIN students s ON e.student_id = s.student_id

JOIN courses c ON e.course_id = c.course_id

WHERE s.grade = '高一' AND e.exam_date = '2024-06-10'

GROUP BY c.course_name

-- 第二步：计算高一年级每个学生的总分

cte_student_total AS (

SELECT

s.student_id,

s.student_name,

s.class,

SUM(e.score) AS total_score -- 学生总分

FROM students s

JOIN exam_scores e ON s.student_id = e.student_id

WHERE s.grade = '高一' AND e.exam_date = '2024-06-10'

GROUP BY s.student_id, s.student_name, s.class

HAVING SUM(e.score) ≥ 240 -- 先筛选总分≥240的学生

-- 第三步：获取高一年级学生的各科成绩（关联第一步的平均分）

cte_student_scores AS (

SELECT

s.student_id,

s.student_name,

c.course_name,

e.score,

g.grade_course_avg

FROM students s

JOIN exam_scores e ON s.student_id = e.student_id

JOIN courses c ON e.course_id = c.course_id

JOIN cte_grade_course_avg g ON c.course_name = g.course_name

WHERE s.grade = '高一' AND e.exam_date = '2024-06-10'

AND e.score ≥ g.grade_course_avg -- 筛选各科高于平均分的记录

)

-- 最终查询：关联第二步（总分）和第三步（各科达标），获取符合条件的学生

SELECT

t.student_name,

t.class,

t.total_score,

-- 用CASE WHEN将各科成绩拼接成一列（方便展示）

GROUP_CONCAT(

CONCAT(sc.course_name, ':', sc.score, '( avg:', ROUND(sc.grade_course_avg,1), ')')

SEPARATOR ' | '

) AS course_scores

FROM cte_student_total t

JOIN cte_student_scores sc ON t.student_id = sc.student_id

-- 确保学生所有参考科目都达标（通过COUNT匹配科目数量）

GROUP BY t.student_id, t.student_name, t.class, t.total_score

HAVING COUNT(sc.course_name) = (

SELECT COUNT(DISTINCT course_name)

FROM cte_grade_course_avg

);

结果：

student_name	class	total_score	course_scores
李四	1 班	180	数学：92 (avg:84.3)

（注：示例中李四总分 180 是因只考了两科，实际场景需根据科目数量调整总分阈值）

优势：多 CTE 将 “算平均分→算总分→筛达标学生” 拆分为 3 个独立模块，每个模块可单独测试（比如先运行cte_grade_course_avg验证平均分是否正确），大幅降低调试难度。

3.3 递归 CTE：处理 “层级数据” 的特殊需求

递归 CTE 是 CTE 的 “进阶技能”，它由 “基础查询（锚点成员）” 和 “递归查询（递归成员）” 两部分组成，能循环处理层级关系数据（如高中的 “年级→班级→学生” 层级、科目分类层级等）。

场景 3：学校有 “年级 - 班级 - 学生” 的三级层级关系（如 “高一→1 班→张三”），需生成所有层级的树形结构列表

首先，我们构造一张层级表grade_class_student（实际场景可通过学生表关联生成）：

id	name	parent_id	type
1	高一	NULL	年级
2	高二	NULL	年级
3	高一 1 班	1	班级
4	高一 2 班	1	班级
5	张三	3	学生
6	李四	3	学生
7	王五	4	学生

用递归 CTE 生成树形结构：

WITH RECURSIVE cte_tree AS (

-- 1. 基础查询（锚点成员）：先获取最顶层的“年级”（parent_id为NULL）

SELECT

id,

name,

parent_id,

type,

CAST(name AS CHAR(100)) AS full_path -- 记录完整层级路径（如“高一→1班→张三”）

FROM grade_class_student

WHERE parent_id IS NULL -- 顶层节点：年级

UNION ALL -- 2. 递归查询（递归成员）：关联子节点（班级→学生）

SELECT

g.id,

g.name,

g.parent_id,

g.type,

-- 拼接路径：父节点路径 + 当前节点名称

CONCAT(ct.full_path, '→', g.name) AS full_path

FROM grade_class_student g

JOIN cte_tree ct ON g.parent_id = ct.id -- 子节点的parent_id = 父节点的id

)

-- 最终查询：展示所有层级的树形结构

SELECT

id,

name,

type,

full_path AS 层级路径

FROM cte_tree

ORDER BY id;

结果：

id	name	type	层级路径
1	高一	年级	高一
2	高二	年级	高二
3	高一 1 班	班级	高一→高一 1 班
4	高一 2 班	班级	高一→高一 2 班
5	张三	学生	高一→高一 1 班→张三
6	李四	学生	高一→高一 1 班→李四
7	王五	学生	高一→高一 2 班→王五