Oracle数据库中树形数据结构的遍历 - 数据库

树形结构，在数据结构反映到生活中是比较常见的，比如超市商品的分类，从大类到中间各级分类到最后的商品，又或者一个公司内部的部门划分等都可以映射为数据结构的数据。下面看一下在Oracle数据库中如何遍历一个树形结构的数据。

假设有这个一个需求，要求统计一个公司每个部门下的员工人数（包括其子部门），假设其中一个部门的结构如下，那么如果要统计这个部门下面有多少员工该怎么做呢？既然是树形结构，那么很容易想起数据结构中我们很树形的二叉树，二叉树是比较基本的一个树形数据结构。处理二叉树的时候，我们只需根据需求遍历即可，同样，如图所示的结构一般称为自由树，而处理这个树形结构依旧是一一遍历即可。

文章正文图片

数据准备
为方便演示，这里先进行一下数据的准备，按照上图所示的结构在数据库中建立好数据，导出的脚本如下：

-- 创建用户表
CREATE TABLE TB_USER
(
  ID             VARCHAR2(50) not null,
  ORG_ID         VARCHAR2(50),
  USER_NAME      VARCHAR2(50)
);

-- 创建组织表
CREATE TABLE TB_ORG
(
   ID           VARCHAR2(50) not null,
   UP_ORG_ID    VARCHAR2(50),
   ORG_NAME     VARCHAR2(50)
);

数据初始化脚本：点此下载

开始遍历
下面，我们先不考虑统计部门人数，只考虑部门，因为树形结构是通过部门来维系的，所以只要遍历出了部门，其他的都好办了。通过浏览Oracle官方文档发现，遍历部门树有两种方式。一种是 connect ... by ... start with ... 语句，官方网站文章内称之为递归的“旧”方法，还有一种是 with ... select ... ，官方网站文章内称之为递归的"新"方法。具体看如下两个查询（部门id 为B69A838684F34197B866C417EE3820AA 的根节点的树）：
查询一：

SELECT 
      ID, 
      UP_ORG_ID 
    FROM 
      TB_ORG CONNECT BY PRIOR ID = UP_ORG_ID              -- 关联条件及顺序（PRIOR位置不同，结果也不一样，因为遍历的方向改变了）
      START WITH ID = 'B69A838684F34197B866C417EE3820AA'  -- 起始点

查询二：

WITH t (ID, UP_ORG_ID) AS(
  SELECT 
    ID, 
    UP_ORG_ID 
  FROM 
    TB_ORG 
  WHERE 
    ID = 'B69A838684F34197B866C417EE3820AA'   -- 起始点
  UNION ALL 
  SELECT 
    org.ID, 
    org.UP_ORG_ID 
  FROM 
    t, 
    TB_ORG org 
  WHERE 
    t.ID = org.UP_ORG_ID                      -- 关联条件
) 
SELECT 
  * 
FROM 
  t

以上两个查询都能得到部门ID为 B69A838684F34197B866C417EE3820AA 的树的所有部门。查询结果如下：
文章正文图片

观察上面两个查询，可以发现其中的共同点，其中比较关键的是起点和关联条件的定义。还是比较容易理解的。到这一步，部门树算是遍历完了，那么下一步就比较容易了，用一个内连接关联用户（职员）即可得到所有关联的职员列表，统计职员人数或者更细致的信息也就很简单了。获取关联用户（职员）列表的查询如下：
方式一：

SELECT 
  b.* 
FROM 
  (
    SELECT 
      ID, 
      UP_ORG_ID 
    FROM 
      TB_ORG CONNECT BY PRIOR ID = UP_ORG_ID START WITH ID = 'B69A838684F34197B866C417EE3820AA'
  ) a 
  INNER JOIN (
    SELECT 
      ID AS USER_Id, 
      ORG_ID, 
      USER_NAME 
    FROM 
      TB_USER
  ) b ON b.ORG_ID = a.ID

方式二：

SELECT 
  userId, 
  ORG_ID, 
  USER_NAME 
from 
  (
        WITH t (ID, UP_ORG_ID) AS(
              SELECT 
                ID, 
                UP_ORG_ID 
              FROM 
                TB_ORG 
              WHERE 
                ID = 'B69A838684F34197B866C417EE3820AA' -- 起始点
          UNION ALL 
              SELECT 
                org.ID, 
                org.UP_ORG_ID 
              FROM 
                t, 
                TB_ORG org 
              WHERE 
                t.ID = org.UP_ORG_ID -- 关联条件
            ) 
        SELECT 
          * 
        FROM 
          t a 
      INNER JOIN (
        SELECT 
          ID as userId, 
          ORG_ID, 
          USER_NAME 
        FROM 
          TB_USER
      ) b ON a.ID = b.ORG_ID
  )

以上两个查询都将得到如下结果：
文章正文图片

得到user列结果其余的信息也就得到了。

总结
树形结构的处理还是很常用的，当然，以上只是我根据自己的理解写的，由于水平有限，描述得可能不是那么清楚，又或者以上语句执行效率不高，请见谅。如果你有好的见解或者更优的处理方法，欢迎留言交流。

最后，留下Oracle网站上看到的文章地址：https://www.oracle.com/technetwork/cn/articles/hartley-recursive-086819-zhs.html#6
里面讲得很详细，也讲了深度和广度遍历的方法，可以查看该文章。