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 '<name>' already exists</name>。
- CTE 必须紧跟在
WITH后面，且前一条语句需以分号结尾（;），否则可能和上一条语句连成非法语法，尤其前面是SET或DECLARE - 不要试图在 CTE 内部修改数据（
UPDATE/INSERT），CTE 是只读结果集，真要更新得用FROM <cte_name></cte_name>语法（仅限 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 或隐式转换——那才是真瓶颈。
