SQL 查询 SQL Server CTEs的语法解析与主要功能介绍

SQL | 查询 SQL Server CTEs的语法解析与主要功能介绍

场景引入：

假设你正在处理一个复杂的销售报表,需要多次引用同一组数据，直接写嵌套子查询？代码会变得又长又难维护，这时候，CTE（Common Table Expression）就像个临时助手，帮你把复杂查询拆解成清晰可读的模块，今天我们就来聊聊 SQL Server 中 CTE 的语法和它能帮你解决哪些实际问题。

CTE 是什么？

CTE（公用表表达式）是一个临时的命名结果集，只在当前查询中有效，你可以把它想象成查询里的“草稿纸”，先定义好一段逻辑，后续随时引用，它的核心优势是提升代码可读性，尤其适合多层嵌套或递归查询的场景。

基础语法

WITH CTE名称 AS (
    SELECT 列1, 列2...
    FROM 表名
    WHERE 条件
)
SELECT * FROM CTE名称; -- 后续使用

关键点：

• WITH 是声明 CTE 的关键字，后跟自定义名称。
• CTE 的定义必须紧接在 WITH 之后，且后续必须跟一个主查询（否则会报错）。
• 可以同时定义多个 CTE，用逗号分隔：

WITH 
    CTE1 AS (SELECT ...),
    CTE2 AS (SELECT ... FROM CTE1)  -- 可以引用前面的CTE
SELECT * FROM CTE2;

CTE 的三大实用功能

简化复杂查询

场景：需要多次引用同一子查询时，避免重复代码。

例子：统计每个部门的平均工资，并筛选出高于平均值的员工。

WITH 部门平均工资 AS (
    SELECT 部门ID, AVG(工资) AS 平均工资
    FROM 员工表
    GROUP BY 部门ID
)
SELECT e.员工姓名, e.工资, d.平均工资
FROM 员工表 e
JOIN 部门平均工资 d ON e.部门ID = d.部门ID
WHERE e.工资 > d.平均工资;

递归查询

场景：处理层级数据（如组织结构、评论树）。

语法：

WITH 递归CTE AS (
    -- 基础部分（起点）
    SELECT 列 FROM 表 WHERE 初始条件
    UNION ALL
    -- 递归部分
    SELECT t.列 
    FROM 表 t
    JOIN 递归CTE r ON t.关联列 = r.列
)
SELECT * FROM 递归CTE;

例子：查询某个员工的所有上级领导（假设表有 员工ID 和 上级ID 字段）：

WITH 领导层级 AS (
    -- 从指定员工开始
    SELECT 员工ID, 上级ID, 0 AS 层级
    FROM 员工表
    WHERE 员工ID = 1001
    UNION ALL
    -- 递归查找上级
    SELECT e.员工ID, e.上级ID, 层级 + 1
    FROM 员工表 e
    JOIN 领导层级 l ON e.员工ID = l.上级ID
)
SELECT * FROM 领导层级;

替代视图或临时表

场景：需要临时存储中间结果，但不想创建视图或物理表。

优势：

• 比临时表轻量,无需显式清理。
• 比视图灵活,可针对单次查询定制逻辑。

注意事项

1. 作用域限制：CTE 仅在定义它的查询中有效，不能跨查询复用。
2. 性能优化：复杂 CTE 可能影响执行计划，可通过索引或查询提示优化。
3. 递归深度：SQL Server 默认限制递归 100 层，可通过 OPTION (MAXRECURSION n) 调整（n=0 表示无限制）。

CTE 是 SQL Server 中提升代码清晰度的利器，尤其适合：
✅ 分解复杂逻辑
✅ 处理递归数据
✅ 避免重复子查询

下次写多层嵌套时,试试用 CTE 让代码像乐高一样模块化吧！