其他

SQL递归查询实战：用WITH子句解锁树形数据遍历

悠悠楠杉

2025-12-12

0 评论

31 阅读

正在检测是否收录...

12/12

正文：

在数据库开发中，处理层级数据就像在迷宫中寻找出口，而递归查询就是那根引导我们穿越复杂结构的金线。特别是当面对组织结构、评论回复链或产品分类这类树形数据时，传统的JOIN操作往往力不从心。这正是SQL递归查询大显身手的舞台。

一、递归查询的前世今生

递归公用表表达式（Recursive CTE）是SQL标准中定义的特殊查询结构，通过WITH子句实现。它的核心思想就像俄罗斯套娃——通过自引用不断展开数据层级，直到满足终止条件。这种查询方式在MySQL 8.0+、PostgreSQL、Oracle和SQL Server等主流数据库中都已得到支持。


-- 基础语法结构
WITH RECURSIVE cte_name AS (
    -- 初始查询（锚成员）
    SELECT columns FROM table WHERE condition
    
    UNION [ALL]
    
    -- 递归部分（递归成员）
    SELECT columns FROM table 
    JOIN cte_name ON join_condition
    WHERE recursion_condition
)
SELECT * FROM cte_name;

二、实战：组织架构树遍历

假设我们有一个包含部门层级的企业组织表：


CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    parent_id INT NULL,
    FOREIGN KEY (parent_id) REFERENCES departments(id)
);

-- 插入测试数据
INSERT INTO departments VALUES
(1, '总公司', NULL),
(2, '技术部', 1),
(3, '市场部', 1),
(4, '前端组', 2),
(5, '后端组', 2),
(6, '数字营销', 3);

场景1：自顶向下查询子树

查找技术部及其所有下级部门：


WITH RECURSIVE dept_tree AS (
    -- 锚点：选择起始节点
    SELECT id, name, parent_id, 1 AS level
    FROM departments 
    WHERE name = '技术部'
    
    UNION ALL
    
    -- 递归：连接子节点
    SELECT d.id, d.name, d.parent_id, dt.level + 1
    FROM departments d
    JOIN dept_tree dt ON d.parent_id = dt.id
)
SELECT * FROM dept_tree ORDER BY level;

场景2：自底向上查询路径

查找"后端组"到根节点的完整路径：


WITH RECURSIVE path_cte AS (
    SELECT id, name, parent_id, CAST(name AS VARCHAR(255)) AS path
    FROM departments 
    WHERE name = '后端组'
    
    UNION ALL
    
    SELECT d.id, d.name, d.parent_id, 
           CONCAT(d.name, ' > ', p.path) AS path
    FROM departments d
    JOIN path_cte p ON d.id = p.parent_id
)
SELECT * FROM path_cte ORDER BY LENGTH(path) DESC;

三、性能优化锦囊

索引是生命线：确保parent_id字段有索引，递归查询性能可提升10倍以上
控制递归深度：添加WHERE level < 5防止无限循环
UNION vs UNION ALL：需要去重时用UNION，但要注意性能损耗
物化提示：在SQL Server中使用OPTION (MAXRECURSION 100)控制最大递归次数

四、高级应用场景

社交网络关系链：查找两人之间的所有关联路径
物料BOM展开：多级产品结构展开计算成本
评论回复树：嵌套评论的完整展示
图数据遍历：地铁线路换乘方案计算


-- 查找所有叶子节点（没有子部门的部门）
WITH RECURSIVE dept_hierarchy AS (
    SELECT id, name, parent_id 
    FROM departments
    WHERE parent_id IS NOT NULL
    
    UNION
    
    SELECT d.id, d.name, d.parent_id
    FROM departments d
    JOIN dept_hierarchy dh ON d.parent_id = dh.id
)
SELECT d.* FROM departments d
LEFT JOIN dept_hierarchy dh ON d.id = dh.parent_id
WHERE dh.id IS NULL;

五、避坑指南

循环引用检测：当数据存在环时（A→B→C→A），查询可能陷入死循环。解决方案是记录已访问节点：


WITH RECURSIVE cycle_detection AS (
    SELECT id, name, parent_id, 
           ARRAY[id] AS path,
           FALSE AS is_cycle
    FROM departments WHERE id = 1
    
    UNION ALL
    
    SELECT d.id, d.name, d.parent_id,
           cd.path || d.id,
           d.id = ANY(cd.path) AS is_cycle
    FROM departments d
    JOIN cycle_detection cd ON d.parent_id = cd.id
    WHERE NOT cd.is_cycle  -- 发现循环立即停止
)
SELECT * FROM cycle_detection;