CTE能替代嵌套子查询做集合运算，但非万能：它本质是逻辑拆分工具，不默认物化，需注意谓词下推、列对齐、避免ORDER BY及重复计算等问题。

CTE 能不能替代嵌套子查询做集合运算

能，但不是所有场景都合适。CTE 本质是命名的临时结果集，不带物化语义（SQL Server 2022+ 可加 MATERIALIZE 提示，但默认不生效），所以它和内联视图在执行计划里经常被展开、重写，和手写子查询没本质区别。

真正起作用的是「逻辑拆分」——把 UNION ALL 前后的大段逻辑分别用 CTE 抽出来，让优化器更容易估算行数、选择连接方式，也方便你肉眼比对各分支输出结构是否对齐。

• 必须保证每个 CTE 分支的列名、数量、类型一致，否则 UNION 会报错 all queries in a UNION must have the same number of columns
• UNION 自动去重，性能差；日常优先用 UNION ALL，去重逻辑交给外层或应用层
• 别在 CTE 里写 ORDER BY（除非配合 OFFSET/FETCH），SQL Server 不允许，会直接报错 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions

怎么写 CTE 才不会让执行计划变差

常见陷阱是把本该走索引查找的条件，挪到 CTE 外层过滤。比如：原查询 WHERE 条件能走 idx_user_status_created，但你把用户状态筛选塞进 CTE，又在外层加时间范围，可能导致 CTE 先扫全表再过滤。

正确做法是「下推谓词」：每个 CTE 尽可能带上业务约束，尤其是高选择率字段（如 status = 'active'、tenant_id = @tenant_id）。

• 避免在 CTE 定义里用 SELECT *，显式列出需要的列，减少内存占用和网络传输
• 如果某个 CTE 被多次引用（比如在 UNION ALL 左右都用到），SQL Server 默认可能重复计算，可考虑用临时表缓存中间结果
• 用 EXISTS 替代 IN (SELECT ...) 配合 CTE，避免空值导致逻辑错误或性能抖动

存储过程中用 CTE 做集合运算要注意啥

CTE 在存储过程里和在单独查询中行为一致，但容易忽略两点：变量作用域和执行上下文。

CTE 本身不能直接引用存储过程变量（如 @start_date），但可以在其内部 SELECT 中使用——只要变量已在外部声明并赋值。真正危险的是把 CTE 当成“函数”反复调用，比如在一个循环里反复定义同名 CTE，SQL Server 会报错 The common table expression '' already exists。

• CTE 必须紧跟在 WITH 后面，且前一条语句需以分号结尾（;），否则可能和上一条语句连成非法语法，尤其前面是 SET 或 DECLARE
• 不要试图在 CTE 内部修改数据（UPDATE/INSERT），CTE 是只读结果集，真要更新得用 FROM 语法（仅限 SQL Server 支持，且有严格限制）
• 调试时别只看 CTE 定义，用 SET STATISTICS XML ON 看实际执行计划，确认各分支是否真的按预期走了索引

什么时候干脆别用 CTE，换方案更稳

当集合运算涉及大量数据（千万级以上）、分支间有强依赖（比如后一个 CTE 要用前一个的聚合结果做阈值），或者需要复用中间结果超过两次，CTE 就开始露怯了。

这时更靠谱的做法是：用 #temp_table 显式控制生命周期，加索引，分步调试。虽然多几行代码，但执行计划稳定、IO 可控、DBA 也看得明白。

• CTE 的递归深度默认上限是 100，超了会报错 The statement terminated. The maximum recursion 100 has been exhausted before statement completion，改用 OPTION (MAXRECURSION n) 是权宜之计，不如拆成迭代
• 跨数据库查询（比如 db1.dbo.t1 UNION ALL db2.dbo.t2）用 CTE 没问题，但注意四部分命名和权限，别漏掉 EXECUTE AS 上下文切换
• 如果最终结果要分页，别在最外层套 OFFSET/FETCH，尽量把分页逻辑下推到单个 CTE 分支里，避免先合并再截断

CTE 简化的是人脑理解成本，不是服务器执行成本。写完记得看执行计划里有没有意料之外的 Table Scan 或隐式转换——那才是真瓶颈。