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
里面讲得很详细,也讲了深度和广度遍历的方法,可以查看该文章。
It's
欢迎访问本站,欢迎留言、分享、点赞。愿您阅读愉快!
*转载请注明出处,严禁非法转载。
https://www.devsong.org
QQ留言 邮箱留言
头像
引用:
取消回复
提交
涂鸦
涂鸦
热门