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

数据准备
为方便演示,这里先进行一下数据的准备,按照上图所示的结构在数据库中建立好数据,导出的脚本如下:
-- 创建用户表
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
里面讲得很详细,也讲了深度和广度遍历的方法,可以查看该文章。