树形结构数据

Oracle树形结构查询

概述

Oracle提供了强大的层次查询功能，通过START WITH和CONNECT BY语句可以轻松处理树形结构数据。本文介绍如何使用Oracle的层次查询来处理组织架构、菜单结构等树形数据。
可以用在机构维度查询，多层节点递归查询的场景。

数据表结构

假设有表tableA包含以下三列：

列名	说明
id	子节点代码（当前节点ID）
name	节点名称
pid	父节点代码（父节点ID）

基础树形查询

简单查询示例

SELECT * FROM tableA a
WHERE 1=1
START WITH a.id = '起始节点代码'
CONNECT BY PRIOR a.id= a.pid
ORDER BY 1;

语法说明：

• START WITH：指定查询的起始节点
• CONNECT BY PRIOR：定义父子关系
• PRIOR a.id= a.pid：表示父节点的ID等于子节点的父ID

高级树形查询

包含层次信息的查询

SELECT a.*,
       connect_by_isleaf                     AS leaf,    -- 判断是否为叶节点，0否1是
       sys_connect_by_path(a.column2, '-')   AS path,    -- 遍历的路径
       connect_by_root(a.column1)            AS root,    -- 遍历根节点
       level                                 AS levels   -- 节点所属树的层数
FROM tableA a
WHERE 1=1
START WITH a.column1 = '起始节点代码'
CONNECT BY PRIOR a.column1 = a.column3
ORDER BY 1;

防止循环查询

SELECT a.*,
       connect_by_isleaf                     AS leaf,
       sys_connect_by_path(a.column2, '-')   AS path,
       connect_by_root(a.column1)            AS root,
       connect_by_iscycle                    AS iscycle, -- 查询树是否有环路
       level                                 AS levels
FROM tableA a
WHERE 1=1
START WITH a.column1 = '起始节点代码'
CONNECT BY NOCYCLE PRIOR a.column1 = a.column3
ORDER BY 1;

层次查询函数详解

1. CONNECT_BY_ISLEAF

• 功能：判断当前节点是否为叶节点
• 返回值：1表示叶节点，0表示非叶节点

2. SYS_CONNECT_BY_PATH

• 功能：显示从根节点到当前节点的完整路径
• 语法：SYS_CONNECT_BY_PATH(column, delimiter)
• 示例：SYS_CONNECT_BY_PATH(name, '/')

3. CONNECT_BY_ROOT

• 功能：返回当前行的根节点值
• 用途：获取整个树结构的根节点信息

4. LEVEL

• 功能：返回当前节点在树中的层级
• 说明：根节点为1，依次递增

5. CONNECT_BY_ISCYCLE

• 功能：检测树结构中是否存在循环
• 返回值：1表示存在循环，0表示无循环
• 注意：使用时必须配合NOCYCLE关键字

实际应用示例

组织架构查询

-- 查询某部门下的所有子部门
SELECT 
    dept_id,
    dept_name,
    parent_dept_id,
    level AS dept_level,
    sys_connect_by_path(dept_name, ' > ') AS dept_path
FROM department
START WITH dept_id = '100'  -- 从总部开始
CONNECT BY PRIOR dept_id = parent_dept_id
ORDER BY level, dept_id;

菜单结构查询

-- 查询完整的菜单树
SELECT 
    menu_id,
    menu_name,
    parent_menu_id,
    level AS menu_level,
    connect_by_isleaf AS is_leaf,
    lpad(' ', (level-1)*2) || menu_name AS menu_display
FROM sys_menu
START WITH parent_menu_id IS NULL  -- 从根菜单开始
CONNECT BY PRIOR menu_id = parent_menu_id
ORDER BY level, menu_id;

查询技巧

1. 向上查询（查找所有父节点）

-- 查找某节点的所有父节点
SELECT * FROM tableA
START WITH id= '子节点代码'
CONNECT BY PRIOR pid= id;  -- 注意：PRIOR位置相反

2. 限制查询层级

-- 只查询前3层
SELECT * FROM tableA
WHERE level <= 3
START WITH id= '起始节点代码'
CONNECT BY PRIOR id= pid;

3. 排除特定分支

-- 排除某个分支
SELECT * FROM tableA
START WITH id= '起始节点代码'
CONNECT BY PRIOR id= pid 
AND column1 != '要排除的节点代码';

注意事项

• 确保表中的父子关系数据正确，避免出现循环引用
• 大数据量时考虑添加适当的WHERE条件限制查询范围
• 使用NOCYCLE关键字防止无限循环
• 合理使用索引提高查询性能

参考资料

本文中参考：Oracle层次查询详解
另，还有一种左右值法构建的树形结构，也很巧妙（优缺点很突出，好查但不好维护，然后新了解的需要稍微理解一下）：数据库无限层级结构左右之值算法