SQL 实战—递归 SQL:层级结构查询与处理树形数据

ops/2024/12/28 2:33:33/

数据库中,树形或层级结构的数据非常常见,如组织架构、分类目录、评论回复等。SQL 提供了递归查询的能力,通过递归通用表表达式(CTE),可以高效地查询和处理树形数据。本文将通过实际案例详细讲解递归 SQL 的应用。


一、递归 SQL 基本概念

1. 什么是递归 SQL?

递归 SQL 是指在 SQL 查询中,自我引用以遍历层级结构或处理递归关系的一种方式。
MySQL 8.0、PostgreSQL 和 SQL Server 等数据库均支持递归查询。

2. 递归 CTE 基本语法
sql">WITH RECURSIVE cte_name (column1, column2, ...) AS (-- 非递归部分(初始查询)初始查询UNION ALL-- 递归部分(递归查询)递归查询(引用 cte_name 本身)
)
SELECT * FROM cte_name;

解释

  • 非递归部分:查询递归的起点(通常是树的根节点)。
  • 递归部分:查询每一层的子节点或下级关系。
  • UNION ALL:将递归结果逐层叠加,直到递归终止。

二、实战案例:组织架构树

1. 数据表结构 employees
idnamemanager_idposition
1AliceNULLCEO
2Bob1CTO
3Carol1CFO
4David2Engineer
5Eve2Engineer
6Frank3Accountant
7Grace4Intern

2. 需求:查询整个组织架构树(层级显示)
目标:获取员工的层级关系,显示路径和层级深度。

3. SQL 实现:递归 CTE 查询
sql">WITH RECURSIVE org_tree AS (-- 非递归部分(根节点)SELECT id, name, manager_id, position, 1 AS level,  CAST(name AS CHAR(255)) AS path  FROM employees  WHERE manager_id IS NULL  -- 根节点,即 CEO  UNION ALL  -- 递归部分SELECT e.id, e.name, e.manager_id, e.position,  t.level + 1 AS level,  CONCAT(t.path, ' -> ', e.name) AS path  FROM employees e  JOIN org_tree t  ON e.manager_id = t.id  
)  
SELECT * FROM org_tree  
ORDER BY level, manager_id;

4. 查询结果
idnamemanager_idpositionlevelpath
1AliceNULLCEO1Alice
2Bob1CTO2Alice -> Bob
3Carol1CFO2Alice -> Carol
4David2Engineer3Alice -> Bob -> David
5Eve2Engineer3Alice -> Bob -> Eve
6Frank3Accountant3Alice -> Carol -> Frank
7Grace4Intern4Alice -> Bob -> David -> Grace

三、逐步剖析递归 SQL 执行过程

  • 第 1 步:非递归部分执行,查找根节点(Alice,CEO)。
  • 第 2 步:递归查找下属,即 manager_id 为 Alice 的员工(Bob 和 Carol)。
  • 第 3 步:继续递归,查找 Bob 和 Carol 的下属(David、Eve、Frank)。
  • 第 4 步:直到没有下属,递归结束。

四、案例 2:分类目录的层级查询

1. 数据表结构 categories
idcategory_nameparent_id
1ElectronicsNULL
2Mobile Phones1
3Laptops1
4iPhone2
5Samsung2
6Dell3
7MacBook3

2. 查询分类目录树(层级展示)
sql">WITH RECURSIVE category_tree AS (SELECT id, category_name, parent_id,  1 AS level,  CAST(category_name AS CHAR(255)) AS path  FROM categories  WHERE parent_id IS NULL  -- 顶级分类  UNION ALL  SELECT c.id, c.category_name, c.parent_id,  t.level + 1 AS level,  CONCAT(t.path, ' -> ', c.category_name) AS path  FROM categories c  JOIN category_tree t  ON c.parent_id = t.id  
)  
SELECT * FROM category_tree  
ORDER BY level, parent_id;

3. 查询结果
idcategory_nameparent_idlevelpath
1ElectronicsNULL1Electronics
2Mobile Phones12Electronics -> Mobile Phones
3Laptops12Electronics -> Laptops
4iPhone23Electronics -> Mobile Phones -> iPhone
5Samsung23Electronics -> Mobile Phones -> Samsung
6Dell33Electronics -> Laptops -> Dell
7MacBook33Electronics -> Laptops -> MacBook

五、应用场景与优化建议

1. 应用场景
  • 组织架构树:查询上下级关系。
  • 分类目录:电商商品分类,处理嵌套分类树。
  • 评论系统:层级评论回复,构建嵌套评论。
  • 权限管理:递归遍历角色与权限关系。

2. 优化建议
  1. 限制递归深度:防止死循环或层级过深导致查询缓慢。
sql">WITH RECURSIVE category_tree AS (SELECT id, category_name, parent_id, 1 AS level FROM categories WHERE parent_id IS NULLUNION ALLSELECT c.id, c.category_name, c.parent_id, t.level + 1FROM categories cJOIN category_tree t ON c.parent_id = t.idWHERE t.level < 5  -- 限制递归深度为 5
)
SELECT * FROM category_tree;
  1. 索引优化:对关联字段(如 parent_id)建立索引,提升递归查询速度。
  2. 提前处理:定期构建物化视图,减少递归查询的频率。

六、总结

  • 递归 SQL 是处理树形和层级数据的有力工具,能简化复杂的层级查询任务。
  • 通过 WITH RECURSIVE 语法,可以高效地遍历父子关系,实现路径计算和深度查询。
  • 在大数据量或深层递归场景中,需要配合索引和查询优化技术,确保递归查询的效率。

http://www.ppmy.cn/ops/145543.html

相关文章

低代码开源项目Joget的研究——Joget7社区版安装部署

大纲 环境准备安装必要软件配置Java配置JAVA_HOME配置Java软链安装三方库 获取源码配置MySql数据库创建用户创建数据库导入初始数据 配置数据库连接配置sessionFactory编译下载tomcat启动下载aspectjweaver移动jw.war文件编写脚本运行 测试参考资料 Joget&#xff0c;作为一款开…

【VScode】第三方GPT编程工具-CodeMoss安装教程

一、CodeMoss是什么&#xff1f; CodeMoss是一款集编程、学习和办公于一体的高效工具。它兼容多种主流平台&#xff0c;包括VSCode、IDER、Chrome插件、Web和APP等&#xff0c;支持插件安装&#xff0c;尤其在VSCode和IDER上的表现尤为出色。无论你是编程新手还是资深开发者&a…

webpack3 webpack4 webpack5 有什么区别

性能优化 Webpack 3 性能优化主要依赖开发者手动配置各种插件。例如&#xff0c;在代码分割方面&#xff0c;需要通过CommonsChunkPlugin来实现公共模块的提取&#xff0c;其配置相对复杂。如果配置不当&#xff0c;可能会导致模块重复打包等问题&#xff0c;影响构建效率和最终…

Linux-Ubuntu之按键中断实验

Linux-Ubuntu之按键中断实验 一&#xff0c; 汇编对中断进行设置二&#xff0c;C语言模块1.中断配置2.GPIO口配置3.按键配置4.主函数 三&#xff0c;总结 一&#xff0c; 汇编对中断进行设置 列出对中断向量表&#xff0c;主要用的是IRQ中断和复位中断服务函数&#xff0c;复位…

什么是ondelete cascade以及使用sqlite演示ondelete cascade使用案例

什么是ondelete cascade ‌ON DELETE CASCADE是数据库中的一种约束&#xff0c;用于自动删除相关的记录‌。具体来说&#xff0c;当一个表中的记录&#xff08;父表&#xff09;被删除时&#xff0c;与其相关的其他表&#xff08;子表&#xff09;中的记录也会被自动删除&…

[python SQLAlchemy数据库操作入门]-11.面向对象方式操作股票数据

哈喽,大家好,我是木头左! 通过ORM,开发者可以使用Python类来表示数据库表,从而使得数据库操作更加直观和易于维护。本文将介绍如何使用SQLAlchemy ORM来操作股票数据。 安装 SQLAlchemy 需要安装SQLAlchemy库。可以使用pip命令进行安装: pip install sqlalchemy定义股票…

如何解决 ‘adb‘ 不是内部或外部命令,也不是可运行的程序或批处理文件的问题

在cmd中输入 adb &#xff0c;显示 ‘adc‘ 不是内部或外部命令&#xff0c;也不是可运行的程序或批处理文件的问题 解决办法&#xff1a;在环境变量中添加adb所在的路径 1、找到 adb.exe 的所在的文件路径&#xff0c;一般在 Android 安装目录下 \sdk\platform-tools\adb.exe…

京准电钟解读,NTP网络授时服务器如何提升DCS系统效率

京准电钟解读&#xff0c;NTP网络授时服务器如何提升DCS系统效率 京准电钟解读&#xff0c;NTP网络授时服务器如何提升DCS系统效率 NTP 网络授时服务器为防火墙内的网络设备、终端、服务器提供准确、可靠和安全的高精度卫星时间参考&#xff0c;可为它支持数万台支持标准的网…