子孙树查询|SQL语句 经典实现子孙树查询的常用SQL写法

🌳 子孙树查询 | SQL语句：经典实现子孙树查询的常用SQL写法

📢 最新动态（2025年7月）
随着数据库技术的演进，递归查询在业务场景中的应用越来越广泛，多家云数据库服务商优化了递归查询性能，使得处理超深层级数据时效率提升高达40%！无论是组织架构、商品分类还是评论回复链，掌握子孙树查询技巧依然是开发者的必备技能。

什么是子孙树查询？

子孙树查询指在树形结构中查找某个节点的所有后代（子节点、孙节点等）。

• 查找某部门的所有下级部门
• 获取某分类下的全部子分类
• 追溯一条微博评论的所有回复

经典SQL实现方案

方案1：递归CTE（通用性强✨）

WITH RECURSIVE tree AS (
    -- 基础查询：选择起始节点
    SELECT id, name, parent_id, 1 AS level
    FROM categories
    WHERE id = 1  -- 从ID=1的节点开始
    UNION ALL
    -- 递归部分：连接子节点
    SELECT c.id, c.name, c.parent_id, t.level + 1
    FROM categories c
    JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY level;

💡 特点

• 支持MySQL 8.0+、PostgreSQL、SQL Server等
• level字段可显示节点深度

方案2：Oracle的CONNECT BY（老牌语法🔮）

SELECT id, name, parent_id, LEVEL
FROM categories
START WITH id = 1  -- 从ID=1的节点开始
CONNECT BY PRIOR id = parent_id  -- 定义父子关系
ORDER SIBLINGS BY name;

🚀 优势

• Oracle原生支持，性能优化好
• LEVEL自动计算深度
• ORDER SIBLINGS BY可对同级节点排序

方案3：路径枚举法（适合固定深度🔍）

若数据存储了路径字符串（如/1/4/7/）：

SELECT * 
FROM categories
WHERE path LIKE '/1/%';  -- 获取ID=1节点的所有子孙

⚠️ 注意

• 需提前维护路径字段
• LIKE查询可能影响性能

性能优化技巧

1. 索引是关键：确保parent_id字段有索引
2. 控制递归深度：添加WHERE level < 5防止无限循环
3. 物化结果：复杂查询可先存入临时表

实战对比（2025年基准测试）

常见问题

❓ Q：如何处理循环引用？
👉 添加CYCLE检测（PostgreSQL）或手动检查路径重复

❓ Q：反向查询父节点链？
👉 将JOIN条件改为ON c.id = t.parent_id