SQL递归CTE使用说明_SQL层级数据查询方法

SQL递归CTE是查询树形结构的核心方法，需严格区分锚点查询与递归查询，列数类型顺序一致，并通过level限制或数据库特定参数防止无限循环。

SQL递归CTE（Common Table Expression）是查询树形结构、组织架构、分类目录等层级数据的核心方法，关键在于正确划分锚点查询（起始行）和递归查询（自关联推进），并设置终止条件防止无限循环。

递归CTE基本结构

必须包含两部分：非递归的初始查询（anchor member） + 用UNION ALL连接的递归查询（recursive member）。整个CTE需定义列名，且两部分列数、类型、顺序须严格一致。

• 语法框架如下：
• WITH RECURSIVE cte_name (col1, col2, ...) AS (
•   -- 锚点查询：获取顶层节点（如 parent_id IS NULL）
•   SELECT id, name, parent_id, 0 AS level
•   FROM categories WHERE parent_id IS NULL
•   UNION ALL
•   -- 递归查询：关联上一层结果，向下延伸
•   SELECT c.id, c.name, c.parent_id, r.level + 1
•   FROM categories c
•   INNER JOIN cte_name r ON c.parent_id = r.id
• )
• SELECT * FROM cte_name ORDER BY level, id;

控制递归深度与防止死循环

多数数据库（如PostgreSQL、SQL Server、MySQL 8.0+）支持MAXRECURSION或类似机制；若不显式限制，可能因数据异常（如父子ID互指）导致查询卡死或报错。

• PostgreSQL：默认无硬限制，但可用LIMIT配合ORDER BY level截断结果
• SQL Server：在OPTION子句中加OPTION (MAXRECURSION 100)
• MySQL：通过系统变量cte_max_recursion_depth控制（默认1000）
• 更稳妥做法是在递归分支中加入level 等条件过滤

常用层级查询场景示例

不只是查“所有子节点”，还可灵活实现路径拼接、祖辈追溯、层级缩进显示等实用效果。

• 生成完整路径：用字符串聚合构建 category_path = '电子/手机/安卓手机'
• 查某节点的所有祖先：把递归方向反过来——从子节点向上JOIN parent_id
• 仅查第N级子节点：WHERE level = N（如WHERE level = 2 查所有孙类目）
• 排除循环引用记录：在递归JOIN时加条件 c.id != r.id 避免自环

基本上就这些。写对锚点和递归关系，注意列对齐和终止逻辑，层级查询就不复杂但容易忽略细节。