讲师中心 微信公众号

工具推荐

视频效率加速
Seko
Seko

AI短剧一键生成成片

立刻MV
立刻MV

低成本歌曲 MV、视频生成

剧云
剧云

中文短剧剧本制作平台

Laper
Laper

剧本策划到成片协作

如何在SQL中利用子查询实现树形层级结构的递归汇总

胖丽酱_2202

胖丽酱_2202

发布时间:2026-11-19 11:55:26

|

358人浏览过

|

来源于php中文网

原创

MySQL 8.0+ 用 WITH RECURSIVE 做树形汇总需分离锚点与递归查询,递归部分仅能通过 JOIN 引用 CTE 自身且不可含聚合函数,汇总须在外层 GROUP BY 或窗口函数中完成。

如何在sql中利用子查询实现树形层级结构的递归汇总

MySQL 8.0+ 怎么用 WITH RECURSIVE 做树形汇总

MySQL 8.0 开始原生支持递归 CTE,这是实现组织架构、分类目录等树形结构汇总最直接的方式。关键不是“能不能”,而是 WITH RECURSIVE 的锚点查询和递归查询必须严格分离,且递归引用只能出现在 UNION ALL 右侧。

常见错误是把初始查询和递归逻辑混写,或者在递归部分用了聚合函数(如 SUM()),这会导致语法报错 Recursive reference to CTE 'xxx' must be in the right operand of UNION ALL。

  • 锚点查询(anchor)必须先选出所有根节点(例如 parent_id IS NULL 或 level = 0)
  • 递归查询(recursive)只能通过 JOIN 关联上一层的 CTE 别名,不能自关联原始表
  • 若需汇总子树数据(如统计每个部门下所有员工薪资总和),得在递归完成后用外层 GROUP BY 聚合,不能在递归内部算
WITH RECURSIVE dept_tree AS (
  SELECT id, name, parent_id, salary, 1 AS level
  FROM departments WHERE parent_id IS NULL
  UNION ALL
  SELECT d.id, d.name, d.parent_id, d.salary, dt.level + 1
  FROM departments d
  INNER JOIN dept_tree dt ON d.parent_id = dt.id
)
SELECT id, name, SUM(salary) AS subtree_salary_sum
FROM dept_tree
GROUP BY id, name;

PostgreSQL 中用 CONNECT BY 的替代方案

PostgreSQL 没有 CONNECT BY,但 WITH RECURSIVE 语义更清晰,且支持路径数组追踪。真正容易被忽略的是循环检测 —— 如果树数据存在脏数据(比如 A → B → A),默认会报错 infinite recursion detected,但不报错不代表没循环,只是没触发阈值。

  • 用 ARRAY[id] 记录访问路径,在递归 JOIN 条件中加 d.id != ALL(dt.path) 主动防环
  • 如果要汇总子节点数量,可在递归时带一个计数器(如 1 AS node_count),再用 SUM(node_count) OVER (PARTITION BY ...) 累加
  • depth 字段建议用 level 或 path_length 命名,避免和 PostgreSQL 内置函数冲突
WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, ARRAY[id] AS path, 1 AS depth
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.path || c.id, t.depth + 1
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
  WHERE c.id != ALL(t.path)  -- 防循环
)
SELECT id, name, depth, array_length(path, 1) AS subtree_size
FROM tree;

SQL Server 的 CTE 递归深度限制怎么调

SQL Server 默认递归上限是 100 层,超过就报错 The statement terminated. The maximum recursion 100 has been exhausted before statement completion.。这不是性能问题,是硬性限制,必须显式改 OPTION (MAXRECURSION n)。

  • 设为 0 表示不限制(慎用,可能死循环)
  • 设为具体数字(如 500)只影响当前查询,不改服务器配置
  • 如果树实际只有 5 层但报了 100 错误,大概率是数据成环,而不是真需要调高
  • 汇总时别在 CTE 内部用 GROUP BY,CTE 本身不支持;得套一层子查询或用窗口函数
WITH OrgChart AS (
  SELECT emp_id, mgr_id, name, salary, 0 AS lvl
  FROM employees WHERE mgr_id IS NULL
  UNION ALL
  SELECT e.emp_id, e.mgr_id, e.name, e.salary, oc.lvl + 1
  FROM employees e
  INNER JOIN OrgChart oc ON e.mgr_id = oc.emp_id
)
SELECT mgr_id, SUM(salary) AS total_underling_salary
FROM OrgChart
GROUP BY mgr_id
OPTION (MAXRECURSION 300);

没有递归 CTE 的老版本 MySQL 怎么办

MySQL 5.7 及更早版本不支持 WITH RECURSIVE,硬上递归逻辑只能靠应用层多次查询或存储过程。但有个实用折中:用固定层数的 LEFT JOIN 展开有限深度树(比如最多 4 级部门),再用 COALESCE 合并字段。

  • 每多一层就要多写一个 JOIN,6 层以上就难维护
  • 汇总时用 SUM(t1.salary + t2.salary + t3.salary) 这种写法,注意 NULL 会污染结果,必须全包 COALESCE(..., 0)
  • 无法动态响应未知深度,一旦新增第 5 级,SQL 就得重写
  • 如果只是查某节点的祖链(向上找父级),用多个 JOIN 比递归还快,因为不用反复扫描

这种方案本质是用空间换时间,且只适用于深度可控、变更不频繁的场景。真要长期用,升级 MySQL 版本比写 20 行 JOIN 更省事。

本站声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

热门AI工具

更多
豆包大模型

豆包大模型是一款由字节跳动推出的企业级大语言模型服务平台。

UP简历
UP简历 Hot

一款AI办公效率工具,主要用于基于AI技术的免费在线简历制作工具,适合需要提升相关任务效率的用户。

咔片AIPPT

一款在线AI演示文稿制作工具,可根据主题和内容需求辅助生成PPT结构与页面,提高演示材料制作效率。

蛙蛙写作

一款AI论文写作工具,主要用于超级AI智能写作助手,适合需要提升相关任务效率的用户。

DeepSeek

DeepSeek是一款面向对话、写作、编程和推理场景的AI大模型工具。

墨刀AI
墨刀AI Hot

一款AI图像与设计工具,主要用于产品经理的专属智能体,适合需要提升相关任务效率的用户。

PixPix
PixPix Hot

PixPix是一款面向电商视觉生产的AI商品图生成工具。

小橡皮AI

一款AI工具,主要用于小橡皮一键去AI味,将机器味重的内容改得更有活人感,防止因为 AI 内容被平台打上 AI 标签,以及限流,适合需要提升相关任务效率的用户。

WorkBuddy

一款AI办公效率工具,主要用于腾讯云推出的AI原生桌面智能体工作台,适合需要提升相关任务效率的用户。

相关专题

更多
数据分析工具有哪些
数据分析工具有哪些

数据分析工具有Excel、SQL、Python、R、Tableau、Power BI、SAS、SPSS和MATLAB等。详细介绍:1、Excel,具有强大的计算和数据处理功能;2、SQL,可以进行数据查询、过滤、排序、聚合等操作;3、Python,拥有丰富的数据分析库;4、R,拥有丰富的统计分析库和图形库;5、Tableau,提供了直观易用的用户界面等等。

5123

2023.10.12

SQL中distinct的用法
SQL中distinct的用法

SQL中distinct的语法是“SELECT DISTINCT column1, column2,...,FROM table_name;”。本专题为大家提供相关的文章、下载、课程内容,供大家免费下载体验。

1031

2023.10.27

SQL中months_between使用方法
SQL中months_between使用方法

在SQL中,MONTHS_BETWEEN 是一个常见的函数,用于计算两个日期之间的月份差。想了解更多SQL的相关内容,可以阅读本专题下面的文章。

1349

2024.02.23

SQL出现5120错误解决方法
SQL出现5120错误解决方法

SQL Server错误5120是由于没有足够的权限来访问或操作指定的数据库或文件引起的。想了解更多sql错误的相关内容,可以阅读本专题下面的文章。

7201

2024.03.06

sql procedure语法错误解决方法
sql procedure语法错误解决方法

sql procedure语法错误解决办法:1、仔细检查错误消息;2、检查语法规则;3、检查括号和引号;4、检查变量和参数;5、检查关键字和函数;6、逐步调试;7、参考文档和示例。想了解更多语法错误的相关内容,可以阅读本专题下面的文章。

3823

2024.03.06

oracle数据库运行sql方法
oracle数据库运行sql方法

运行sql步骤包括:打开sql plus工具并连接到数据库。在提示符下输入sql语句。按enter键运行该语句。查看结果,错误消息或退出sql plus。想了解更多oracle数据库的相关内容,可以阅读本专题下面的文章。

7200

2024.04.07

sql中where的含义
sql中where的含义

sql中where子句用于从表中过滤数据,它基于指定条件选择特定的行。想了解更多where的相关内容,可以阅读本专题下面的文章。

10101

2024.04.29

sql中删除表的语句是什么
sql中删除表的语句是什么

sql中用于删除表的语句是drop table。语法为drop table table_name;该语句将永久删除指定表的表和数据。想了解更多sql的相关内容,可以阅读本专题下面的文章。

1370

2024.04.29

GoFrame框架Web开发与路由中间件
GoFrame框架Web开发与路由中间件

本专题聚焦GoFrame框架的Web开发,拆解路由注册与匹配规则、请求参数获取、参数校验与中间件执行顺序,说明统一响应、JWT鉴权、文件上传与接口文档生成的落地方式,并补充优雅重启与测试实践。

20

2026.11.17

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
热门推荐
/
最新课程
关于我们 免责申明 举报中心 意见反馈 讲师合作 广告合作 最新更新
php中文网:公益在线php培训,帮助PHP学习者快速成长!
关注服务号
PHP中文网订阅号
每天精选资源文章推送

Copyright 2014-2026 https://www.php.cn/ All Rights Reserved | php.cn