企业官网建设流程全解析

• 文档
• 教程
• 知识库

导读

本文以 til 仓库中 postgres/fizzbuzz-with-common-table-expressions.md 为核心，深入讲解 PostgreSQL 中WITH RECURSIVE（递归通用表表达式）的原理与实战用法。读者将通过一个完整的 FizzBuzz 实现，掌握递归 CTE 的锚点成员、递归成员、UNION去重语义与终止条件，并理解它与generate_series()等常规序列生成方案的差异，从而学会用纯 SQL 解决循环与递推类问题。

什么是 CTE 与递归 CTE

CTE（Common Table Expression，通用表表达式）是 PostgreSQL 中一种临时命名的查询结果集，使用WITH关键字定义，可以在后续的SELECT、INSERT、UPDATE等语句中引用，用于拆分复杂查询、提升可读性，或作为数据加工的中转站。

普通的 CTE 只定义一次查询，而递归 CTE允许该查询引用自身，反复迭代直到满足终止条件。其语法结构为：

WITH RECURSIVE cte_name (col1, col2, ...) AS ( -- 锚点成员（anchor member）：非递归部分，生成初始行 SELECT ... UNION [ALL] -- 递归成员（recursive member）：引用 cte_name 自身，生成后续行 SELECT ... FROM cte_name WHERE ... ) SELECT ... FROM cte_name;

一个递归 CTE 通常包含三个要素：

1. 锚点成员（Anchor Member）：位于UNION之前的首个SELECT，负责生成递归的起点（第一轮结果）。
2. 递归成员（Recursive Member）：位于UNION之后、引用了自身名称的SELECT，每一轮迭代都基于上一轮的结果继续计算。
3. 终止条件：通常写在递归成员的WHERE子句中，当不再产生新行时迭代自然停止。

用递归 CTE 求解 FizzBuzz：完整代码拆解

FizzBuzz 是经典的编程练习题：对 1 到 N 的每个整数，若能同时被 3 和 5 整除输出fizzbuzz，能被 5 整除输出buzz，能被 3 整除输出fizz，否则输出数字本身。原文档给出的 PostgreSQL 递归 CTE 解法如下：

WITH RECURSIVE fizzbuzz (num, val) AS ( SELECT 0, '' UNION SELECT (num + 1), CASE WHEN (num + 1) % 15 = 0 THEN 'fizzbuzz' WHEN (num + 1) % 5 = 0 THEN 'buzz' WHEN (num + 1) % 3 = 0 THEN 'fizz' ELSE (num + 1)::text END FROM fizzbuzz WHERE num < 100 ) SELECT val FROM fizzbuzz WHERE num > 0;

下面逐段分析其工作原理。

锚点成员：初始化递归起点

SELECT 0, ''

这一行是递归的第一轮，向结果集中放入初始记录(num = 0, val = '')。num = 0作为计数器起点，val为空字符串占位，最终查询时会通过WHERE num > 0把它过滤掉。

递归成员：生成下一轮数据

SELECT (num + 1), CASE WHEN (num + 1) % 15 = 0 THEN 'fizzbuzz' WHEN (num + 1) % 5 = 0 THEN 'buzz' WHEN (num + 1) % 3 = 0 THEN 'fizz' ELSE (num + 1)::text END FROM fizzbuzz WHERE num < 100

• 递归成员每轮从当前的fizzbuzz结果中取num，加 1 得到下一个要判定的数字；
• CASE表达式按顺序判定整除规则（注意% 15必须先判断，否则 15 的倍数会先落入% 3或% 5分支，见下文“判断顺序”一节）；
• ELSE (num + 1)::text将整数转换为文本，保证val列的类型一致性；
• WHERE num < 100是终止条件：只要最新一轮的最大num小于 100，迭代就继续；当num达到 100 时不再产生新行，递归自动结束。

主查询：过滤起点并输出

SELECT val FROM fizzbuzz WHERE num > 0;

递归结束后，CTE 结果集中包含num从 0 到 100 的 101 行记录。主查询通过WHERE num > 0剔除锚点行，最终按num升序输出 1 到 100 的 FizzBuzz 序列。由于 CTE 天然按迭代轮次生成行，输出顺序与num的递增顺序一致，无需额外ORDER BY。

三个必须注意的细节

1. CASE 分支的判定顺序：% 15必须最先判断

CASE表达式按分支出现的先后顺序匹配，命中即返回，不再继续判断。15 的倍数同时满足% 3 = 0与% 5 = 0，因此% 15分支必须放在最前面。若把顺序颠倒，例如先判断% 3，15 就会被错误地输出为fizz，这是 FizzBuzz 实现中最高频的 bug。

2. 为什么用UNION而不是UNION ALL

原文档的递归 CTE 使用的是UNION。PostgreSQL 对递归 CTE 的UNION会进行行去重：每一轮新生成的(num, val)若已存在于之前的结果中，则被丢弃。由于每一轮产生的num严格递增、互不重复，这里的UNION去重不会影响结果，但它的真正作用在于阻止无限递归——当某轮无法产生任何尚未出现的新行时，递归自然终止。

关于UNION与UNION ALL的语义差异，可参考仓库中的 union-all-rows-including-duplicates.md：UNION会合并并去重，而UNION ALL保留全部重复行。若递归逻辑可能生成重复行且没有显式终止条件，使用UNION是防止死循环的关键手段。

3. 列类型一致性

CTE 定义了显式列名(num, val)。锚点成员给出''（text 类型），递归成员的ELSE分支通过::text强制转换保证val列始终为文本类型。若分支类型不一致，PostgreSQL 会报错或产生隐式转换问题，显式::text是稳妥做法。

横向对比：递归 CTE vs generate_series()

对于“生成 1 到 100 的序列”这一需求，PostgreSQL 其实有更轻量的内置函数generate_series()。仓库中的 generate-series-of-numbers.md 展示了它的基本用法：

SELECT generate_series(1, 5);

该函数支持start、stop与可选step参数，例如generate_series(5, 1, -1)倒序生成、generate_series(3, 17, 3)生成 3 的倍数。基于它，FizzBuzz 可以改写为：

SELECT CASE WHEN g % 15 = 0 THEN 'fizzbuzz' WHEN g % 5 = 0 THEN 'buzz' WHEN g % 3 = 0 THEN 'fizz' ELSE g::text END FROM generate_series(1, 100) AS g;

两者对比如下：

适用前提说明：当任务仅仅是“生成一段递增整数”时，generate_series()更简洁高效；而当计算需要依赖上一轮的结果（例如斐波那契数列、层级树展开、依赖前置状态的递推）时，递归 CTE 才是不可替代的解法。FizzBuzz 本身两种方案都能实现，原文档选择递归 CTE 的目的正是演示其迭代机制。

递归 CTE 的更多应用与扩展思路

掌握了递归 CTE 的机制后，可以将其推广到更实际的场景：

• 斐波那契数列等递推计算：递归成员中同时引用前两轮（或多轮）的值，通过多列递推；
• 树形结构与层级遍历：从根节点出发，每轮JOIN子表并累加路径，常用于组织架构、分类树、评论楼的无限层级展开；
• 日历/时间序列展开：锚点取起始日期，递归成员按固定间隔累加日期，直到超过截止日期——可结合仓库中 intervals-of-time-by-week.md 的 interval 构造方式，生成周粒度时间序列；
• 数据清洗与补齐：递归填充缺失序号、补齐连续编号。

此外，CTE 并非只能承载递归查询，普通WITH也可作为子查询的命名替身，与 sets-with-the-values-command.md 中介绍的VALUES命令组合使用（VALUES可以在 CTE 内部创建多行多列的结构化数据源），以及结合 count-the-number-of-trues-in-an-aggregate-query.md 中CASE表达式与聚合函数配合的写法，共同构成 SQL 数据加工的常用工具箱。

实践建议与注意事项

1. 务必设置终止条件：递归成员必须包含能逐步收敛的WHERE条件，否则查询会无限迭代直至超时或触发资源限制。对不确定深度的场景，可考虑在 CTE 中额外维护深度列并限制最大层数。
2. 优先考虑UNION去重语义：当递归过程中可能出现重复行时，UNION会自动终止重复分支，这既是去重也是保护机制；确定无重复时也可使用UNION ALL以省去去重开销。
3. 验证运行环境：递归 CTE 属于标准 SQL 特性，在 PostgreSQL 中自 8.4 起稳定支持；文中 SQL 可直接在psql或任意 PostgreSQL 客户端中执行验证（例如通过psql连接后粘贴执行）。具体语法细节以你所用 PostgreSQL 版本的官方文档为准。
4. 性能考量：递归 CTE 是逐轮迭代执行，数据量大时开销不容忽视。对于“纯序列生成”场景，优先选用generate_series()等集合返回函数；仅在确实需要跨轮递推时才使用递归 CTE。

本文的完整示例源码位于仓库 postgres/fizzbuzz-with-common-table-expressions.md，配合上述分析，你可以在任何 PostgreSQL 环境中复现 1 到 100 的 FizzBuzz 输出，并将递归 CTE 的思路迁移到层级遍历、递推计算等真实业务场景中。

• 文档
• 教程
• 知识库