在数据库中树形或层级结构的数据非常常见如组织架构、分类目录、评论回复等。SQL 提供了递归查询的能力通过递归通用表表达式CTE可以高效地查询和处理树形数据。本文将通过实际案例详细讲解递归 SQL 的应用。一、递归 SQL 基本概念1. 什么是递归 SQL递归 SQL 是指在 SQL 查询中自我引用以遍历层级结构或处理递归关系的一种方式。MySQL 8.0、PostgreSQL 和 SQL Server 等数据库均支持递归查询。2. 递归 CTE 基本语法WITH RECURSIVE cte_name (column1, column2, ...) AS ( -- 非递归部分初始查询 初始查询 UNION ALL -- 递归部分递归查询 递归查询引用 cte_name 本身 ) SELECT * FROM cte_name;解释非递归部分查询递归的起点通常是树的根节点。递归部分查询每一层的子节点或下级关系。UNION ALL将递归结果逐层叠加直到递归终止。二、实战案例组织架构树1. 数据表结构employeesidnamemanager_idposition1AliceNULLCEO2Bob1CTO3Carol1CFO4David2Engineer5Eve2Engineer6Frank3Accountant7Grace4Intern2. 需求查询整个组织架构树层级显示目标获取员工的层级关系显示路径和层级深度。3. SQL 实现递归 CTE 查询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_idpositionlevelpath1AliceNULLCEO1Alice2Bob1CTO2Alice - Bob3Carol1CFO2Alice - Carol4David2Engineer3Alice - Bob - David5Eve2Engineer3Alice - Bob - Eve6Frank3Accountant3Alice - Carol - Frank7Grace4Intern4Alice - Bob - David - Grace三、逐步剖析递归 SQL 执行过程第 1 步非递归部分执行查找根节点AliceCEO。第 2 步递归查找下属即 manager_id 为 Alice 的员工Bob 和 Carol。第 3 步继续递归查找 Bob 和 Carol 的下属David、Eve、Frank。第 4 步直到没有下属递归结束。四、案例 2分类目录的层级查询1. 数据表结构categoriesidcategory_nameparent_id1ElectronicsNULL2Mobile Phones13Laptops14iPhone25Samsung26Dell37MacBook32. 查询分类目录树层级展示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_idlevelpath1ElectronicsNULL1Electronics2Mobile Phones12Electronics - Mobile Phones3Laptops12Electronics - Laptops4iPhone23Electronics - Mobile Phones - iPhone5Samsung23Electronics - Mobile Phones - Samsung6Dell33Electronics - Laptops - Dell7MacBook33Electronics - Laptops - MacBook五、应用场景与优化建议1. 应用场景组织架构树查询上下级关系。分类目录电商商品分类处理嵌套分类树。评论系统层级评论回复构建嵌套评论。权限管理递归遍历角色与权限关系。2. 优化建议限制递归深度防止死循环或层级过深导致查询缓慢。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;索引优化对关联字段如parent_id建立索引提升递归查询速度。提前处理定期构建物化视图减少递归查询的频率。六、总结递归 SQL 是处理树形和层级数据的有力工具能简化复杂的层级查询任务。通过WITH RECURSIVE语法可以高效地遍历父子关系实现路径计算和深度查询。在大数据量或深层递归场景中需要配合索引和查询优化技术确保递归查询的效率。