CTE SQL Guide: Syntax, Recursion, and Refactoring CTE SQL 完整指南:语法、递归、依赖与查询重构

What is a CTE in SQL? SQL 中的 CTE 是什么?

A CTE is primarily a query-structure tool, not a promise to store data or improve performance. It can make grain, dependencies, and transformations visible, but a poorly designed chain of CTEs can merely spread complexity across more names. Each stage still needs a clear purpose, input grain, output grain, and validation rule.

CTE 首先是查询结构工具,并不承诺存储数据或提高性能。它可以让粒度、依赖与转换更加可见,但设计不良的 CTE 链只会把复杂度分散到更多名字中。每个阶段仍然需要明确的用途、输入粒度、输出粒度与验证规则。

WITH completed_orders AS (
  SELECT order_id, customer_id, order_date, amount
  FROM orders
  WHERE status = 'completed'
)
SELECT customer_id, SUM(amount) AS revenue
FROM completed_orders
GROUP BY customer_id;

Read a CTE as a named query stage 把 CTE 理解为命名查询阶段

The WITH clause appears before the main statement. Each CTE has a name, an optional output-column list, and a query body. Later CTEs can usually reference earlier CTEs in the same clause, forming a directed dependency chain. The final SELECT , INSERT , UPDATE , DELETE , or vendor-supported statement consumes one or more of those results.

WITH 位于主语句之前。每个 CTE 包含名称、可选输出字段列表和查询主体。同一 WITH 中后面的 CTE 通常可以引用前面的 CTE,从而形成有向依赖链。最终的 SELECT 、 INSERT 、 UPDATE 、 DELETE 或数据库支持的其他语句会消费其中一个或多个结果。

Design multiple CTEs as a dependency graph 把多个 CTE 设计成依赖图

Multiple CTEs are separated by commas under one WITH . Use them to reveal meaningful transformations: define the eligible population, reduce one-to-many facts, attach dimensions, calculate a window result, then shape the final output. Avoid a long serial chain where each stage adds only one cosmetic expression; excessive fragmentation forces readers to jump between names without reducing conceptual load.

多个 CTE 在同一个 WITH 下以逗号分隔。可以用它们展示有意义的转换:定义合格总体、缩减一对多事实、关联维度、计算窗口结果,再形成最终输出。不要建立每层只增加一个装饰表达式的长串链条;过度拆分会迫使读者不断跳转,却没有降低概念负担。

WITH eligible_orders AS (
  SELECT order_id, customer_id, order_date, amount
  FROM orders
  WHERE status = 'completed'
),
customer_totals AS (
  SELECT customer_id,
         COUNT(*) AS order_count,
         SUM(amount) AS revenue
  FROM eligible_orders
  GROUP BY customer_id
),
ranked_customers AS (
  SELECT customer_id, order_count, revenue,
         DENSE_RANK() OVER (
           ORDER BY revenue DESC
         ) AS revenue_tier
  FROM customer_totals
)
SELECT *
FROM ranked_customers
WHERE revenue_tier <= 10;

The stage names communicate a contract. eligible_orders is one row per completed order; customer_totals is one row per customer; ranked_customers preserves that customer grain and adds a tier. Reviewers can test each transition rather than reasoning through one deeply nested expression.

阶段名称形成了清晰契约: eligible_orders 每个已完成订单一行, customer_totals 每个客户一行, ranked_customers 保持客户粒度并增加层级。审查者可以逐段验证,而不必一次理解深层嵌套表达式。

Build a recursive CTE with a safe stopping rule 使用安全终止规则构建递归 CTE

A recursive CTE has an anchor term that creates the initial rows and a recursive term that joins new rows to the prior result. A set operator—commonly UNION ALL —combines them. Evaluation continues until the recursive term produces no new rows or a database-specific recursion boundary stops it.

递归 CTE 包含产生初始记录的锚点项,以及把新记录连接到上一轮结果的递归项;通常使用 UNION ALL 组合。执行会持续到递归项不再产生新记录,或达到数据库特定的递归边界。

WITH RECURSIVE org_tree AS (
  SELECT employee_id, manager_id, employee_name,
         0 AS depth,
         ARRAY[employee_id] AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.employee_id, e.manager_id, e.employee_name,
         t.depth + 1,
         t.path || e.employee_id
  FROM employees e
  JOIN org_tree t ON e.manager_id = t.employee_id
  WHERE NOT e.employee_id = ANY(t.path)
    AND t.depth < 100
)
SELECT * FROM org_tree;

This PostgreSQL-style example records a path to reject cycles and adds a defensive depth boundary. Exact array, cycle-detection, and recursion syntax varies by platform. The PostgreSQL WITH-query documentation explains recursive evaluation, search order, cycle handling, and materialization controls. Always test self-loops, longer cycles, orphan nodes, multiple roots, deep chains, and duplicate edges.

该 PostgreSQL 风格示例记录路径以拒绝循环,并增加防御性深度边界。数组、循环检测和递归语法会因平台而异。PostgreSQL WITH 查询官方文档解释了递归执行、搜索顺序、循环处理与物化控制。务必测试自循环、长循环、孤儿节点、多根节点、深链和重复边。

Choose between a CTE, subquery, view, and temp table 在 CTE、子查询、视图与临时表之间选择

These constructs can express similar transformations but have different scope and operational behavior. Choose by reuse, observability, statistics, lifecycle, permissions, and portability—not by a blanket claim that one is always faster.

这些结构可以表达相似转换,但作用域和运行行为不同。应根据复用、可观测性、统计信息、生命周期、权限与可移植性选择,而不是相信某一种永远更快。

A compact subquery may be clearer than a one-line CTE used once. A temp table may be better when a costly intermediate result is reused many times and needs statistics or indexes. A view may formalize governed logic shared across teams. CTEs excel when the logic belongs to one statement and named stages materially improve reasoning.

只使用一次的简单逻辑可能用紧凑子查询更清楚;高成本中间结果被多次访问且需要统计或索引时,临时表可能更好;跨团队共享治理逻辑可以使用视图;逻辑仅属于一条语句且命名阶段能明显提升理解时,CTE 最合适。

Refactor nested SQL into meaningful CTE stages 把嵌套 SQL 重构为有意义的 CTE 阶段

Do not mechanically turn every pair of parentheses into a CTE. First identify the decision population and current output grain. Then mark transformations that change eligibility, row count, grain, or meaning. Those boundaries are strong candidates for named stages.

不要机械地把每一对括号都变成 CTE。应先确定决策总体和当前输出粒度,再标记改变资格、行数、粒度或含义的转换;这些边界才适合命名阶段。

1. Freeze expected behavior. Save representative inputs, expected rows, totals, null behavior, and tie rules.
2. Map the nested query. Identify joins, filters, aggregates, windows, correlated references, and repeated expressions.
3. Name semantic boundaries. Extract stages such as eligible events, daily totals, latest records, or ranked candidates.
4. Project only needed columns. Narrow each contract so later stages cannot accidentally depend on irrelevant data.
5. Validate every boundary. Compare counts and business keys against the original query and independent expectations.
6. Compare execution evidence. Review actual plans and benchmark representative workloads before claiming improvement.

1. 冻结预期行为。 保存代表性输入、预期记录、总量、空值行为和并列规则。
2. 绘制嵌套查询结构。 识别连接、过滤、聚合、窗口、相关引用和重复表达式。
3. 命名语义边界。 提取合格事件、每日汇总、最新记录或排名候选等阶段。
4. 只保留必要字段。 缩窄每个契约,避免后续阶段依赖无关数据。
5. 验证每个边界。 把行数与业务键同时对照原查询和独立预期。
6. 比较执行证据。 检查实际计划并对代表性负载做基准测试,再判断是否改进。

Recognize CTE anti-patterns before they spread 在 CTE 反模式扩散前识别它们

CTEs improve readability only when their boundaries match reasoning boundaries. A stage named data that selects every column, joins five tables, calculates several metrics, and filters unrelated populations is still a monolith. Conversely, splitting each expression into a separate one-line CTE creates navigational overhead without adding a meaningful contract.

只有当 CTE 边界与推理边界一致时,它才会提升可读性。一个名为 data 的阶段如果选择全部字段、连接五张表、计算多个指标并过滤不同总体,仍然是单体查询;反过来,把每个表达式拆成单行 CTE 也只会增加跳转成本,而没有形成有意义契约。

Another warning sign is a CTE used as an informal security boundary. Unless the database feature and permissions explicitly guarantee isolation, later query logic may still expose columns or rows. Access control belongs in governed database policies, secure views, or authorized layers—not in a naming convention.

另一个危险信号是把 CTE 当成非正式安全边界。除非数据库功能与权限明确保证隔离,否则后续逻辑仍可能暴露字段或记录。访问控制应放在受治理的数据库政策、安全视图或授权层,而不是依赖命名约定。

Finally, do not preserve a CTE merely because it once fixed a plan. Engine upgrades, statistics, parameter distributions, and schema changes can invalidate that outcome. Keep performance-sensitive structure backed by reproducible benchmarks and comments that state the observed reason, tested version, and fallback—not folklore.

最后,不要仅因为某个 CTE 曾经改善执行计划就永久保留它。引擎升级、统计信息、参数分布和模式变化都可能让该结果失效。对性能敏感的结构应有可复现基准和注释,说明观察到的原因、测试版本与回退方案,而不是依赖经验传说。

Do not assume a CTE is materialized or faster 不要假设 CTE 一定物化或更快

CTE optimization behavior differs across databases and versions. Some engines inline eligible nonrecursive CTEs into the parent query, allowing predicates and joins to be optimized together. Some materialize under specific conditions or hints. A CTE referenced multiple times may be recalculated, shared, spooled, or transformed. Recursive CTEs have different execution needs from nonrecursive ones.

CTE 优化行为因数据库和版本而异。有些引擎会把符合条件的普通 CTE 内联到父查询,让条件与连接一起优化;有些会在特定条件或提示下物化。被多次引用的 CTE 可能重新计算、共享、写入中间结构或被改写;递归 CTE 与普通 CTE 的执行需求也不同。

Read the documentation for the exact engine and version. For SQL Server syntax and usage rules, see Microsoft's common table expression reference . Treat plans, runtime metrics, and result equivalence as evidence; treat "CTEs are faster" or "CTEs are always optimization fences" as unsafe generalizations.

请阅读实际引擎与版本的文档。SQL Server 的语法和使用规则可参考 Microsoft 公用表表达式官方文档。应把执行计划、运行指标和结果等价性视为证据,而把"CTE 更快"或"CTE 永远是优化屏障"视为不可靠概括。

Validate a CTE query before production 上线前验证 CTE 查询

• Check that every stage has a semantic name and documented row grain.
• Compare total rows, distinct keys, null rates, and aggregates at every stage boundary.
• Confirm filters are applied before or after ranking and aggregation according to the business question.
• For recursion, test cycles, orphans, duplicate edges, multiple roots, maximum depth, and termination.
• Inspect repeated references, wide projections, sorts, spills, and estimated versus actual rows.
• Compare the refactored query against expected outcomes, not only against a potentially wrong original.

• 确认每个阶段拥有语义化名称和已记录行粒度。
• 在每个边界比较总行数、不同键、空值比例和聚合值。
• 根据业务问题确认过滤位于排名与聚合之前还是之后。
• 递归场景测试循环、孤儿、重复边、多根、最大深度与终止。
• 检查重复引用、宽字段、排序、溢写以及估算与实际行数。
• 把重构查询与预期结果比较,而不只是与可能错误的原查询比较。

CTE SQL frequently asked questions CTE SQL 常见问题

Continue with a related guide 继续阅读相关指南

Use these closely related guides to verify the next layer of the workflow without mixing separate troubleshooting or decision intents. 可继续阅读以下紧密相关的指南,验证工作流的下一层,同时避免混淆不同的排障或决策意图。

• SQL subqueries SQL 子查询
• Nested SQL queries 嵌套 SQL 查询
• Correlated subqueries 相关子查询