本文介绍了Oracle数据库中处理树形结构数据的方法，包括树的定义、节点的关系、树的表定义，以及如何通过SQL查询获取根节点和所有父节点等信息。
Oracle 树查询
一、树的定义
树是一种包括祖父节点、父节点、子节点、孙节点的一种数据结构。通常定义为具有n个节点的有限集合。
度是树的节点的一种分类，
节点的度：一个节点拥有的子节点即成为节点的度，比如A节点，有B和C两个子节点，那么A节点的度=2。
节点间关系
子节点：某一个节点的子节点称为孩子节点。比如B、C节点是A节点的孩子节点。
父节点：与孩子节点相反。比如，A节点是B、C的父节点。
兄弟节点：同一个双亲节点的孩子节点，之间称为兄弟节点。比如，B、C为兄弟节点。
二、树的表定义
一个树的数据类型，一般用表可以定义出来，通常定义的表可以包括下面的内容：
CREATE TABLE tb_Tree
(
fPID VARCHAR2(6), -- 节点ID Position ID
fFPID VARCHAR2(6), -- 父节点ID Father Position ID
fArea VARCHAR2(1), -- 区域 Father Area
fLayer Number, -- 层
fMID VARCHAR2(6), -- 成员ID Member ID
fRDate Date, -- 注册时间 Register Date
fStatus Number, -- 节点状态
CONSTRAINT pk_Tree PRIMARY KEY (fPID)
);
Comment On Column tb_Tree.fPID is '节点ID';
Comment On Column tb_Tree.fFPID is '父节点ID';
Comment On Column tb_Tree.fLayer is '层';
Comment On Column tb_Tree.fMID is '成员ID';
Comment On Column tb_Tree.fRDate is '注册时间';
Comment On Column tb_Tree.fStatus is '节点状态';三、样本数据
INSERT INTO tb_TREE VALUES ('E00000','ROOT', '0',01,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00001','E00000','A',02,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00002','E00000','B',02,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00003','E00001','A',03,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00004','E00001','B',03,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00005','E00002','A',03,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00006','E00002','B',03,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00007','E00003','A',04,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00008','E00003','B',04,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00009','E00004','A',04,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00010','E00004','B',04,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00011','E00005','A',04,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00012','E00005','B',04,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00013','E00006','A',04,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00014','E00006','B',04,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00015','E00007','A',05,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00016','E00007','B',05,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00017','E00008','A',05,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00018','E00008','B',05,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00019','E00009','A',05,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00020','E00009','B',05,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00021','E00010','A',05,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00022','E00010','B',05,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00023','E00011','A',05,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00024','E00011','B',05,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00025','E00012','A',05,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00026','E00012','B',05,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00027','E00013','A',05,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00028','E00013','B',05,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00029','E00014','A',05,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00030','E00014','B',05,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00031','E00015','A',06,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00032','E00015','B',06,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00033','E00016','A',06,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00034','E00016','B',06,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00035','E00017','A',06,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00036','E00017','B',06,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00037','E00018','A',06,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00038','E00018','B',06,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00039','E00019','A',06,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00040','E00019','B',06,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00041','E00020','A',06,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00042','E00020','B',06,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00043','E00021','A',06,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00044','E00021','B',06,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00045','E00022','A',06,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00046','E00022','B',06,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00047','E00023','A',06,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00048','E00023','B',06,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00049','E00024','A',06,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00050','E00024','B',06,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00051','E00025','A',06,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00052','E00025','B',06,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00053','E00026','A',06,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00054','E00026','B',06,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00055','E00027','A',06,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00056','E00027','B',06,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00057','E00028','A',06,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00058','E00028','B',06,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00059','E00029','A',06,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00060','E00029','B',06,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00061','E00030','A',06,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);
INSERT INTO tb_TREE VALUES ('E00062','E00030','B',06,'M00001',TO_DATE('2017-07-01','YYYY-MM-DD'),0);四、根节点的特殊性
一个树，定义其根节点的方法有多种，但是必须有一个特殊的含义，在这里，根节点设计了fPID = ‘E00000’。我们定义这个树只有一个根节点。
查询根节点：
SELECT * FROM tb_Tree WHERE fFPID = 'ROOT';
SELECT * FROM tb_Tree WHERE fArea = '0';
SELECT * FROM tb_Tree WHERE fPID = 'E00000';五、查询所有父节点、层和区域
语法：
select...start with... connect by ...prior
依托于该语法，我们可以将一个表形结构的中以树的顺序列出来。
在下面列述了 Oracle 中树型查询的常用查询方式以及经常使用的与树查询相关的Oracle特性函数等，在这里只涉及到一张表中的树查询方式而不涉及多表中的关联等。
SELECT fFPID,fLayer,fArea FROM tb_Tree WHERE fFPID <> 'ROOT' START WITH fPID = 'E00062' CONNECT BY PRIOR fFPID = fPID;
查询结果如下
序号 父节点 层 区域
1 E00030 6 B
2 E00014 5 B
3 E00006 4 B
4 E00002 3 B
5 E00000 2 B
我们从最基本的操作，逐步列出树查询中常见的操作，所以查询出来的节点以家族中的辈份作比方。
1. 查找树中的所有顶级父节点（辈份最长的人）。 假设这个树是个目录结构，那么第一个操作总是找出所有的顶级节点，再根据该节点找到其下属节点。
SELECT * FROM tb_Tree WHERE fFPID IS NULL;
2. 查找一个节点的直属子节点（所有儿子）。 如果查找的是直属子类节点，也是不用用到树型查询的。
SELECT * FROM tb_Tree WHERE fFPID = 'E00000';
3. 查找一个节点的所有直属子节点（所有后代）。
SELECT * FROM tb_Tree START WITH fPID = 'E00002' CONNECT BY fFPID = PRIOR fPID;
这个查找的是fPID为 E00002 的节点下的所有直属子类节点，包括子辈的和孙子辈的所有直属节点。
下面这个语句和上面的语句是具有相同的功能，都是查询E00002的所有子节点的：
WITH CTE ( fPID, fFPID, lev) AS
(
SELECT fPID, fFPID, 1 AS lev FROM tb_Tree WHERE fPID = 'E00002'
UNION ALL
SELECT E.fPID, E.fFPID, lev + 1 FROM CTE T, tb_Tree E
WHERE T.fPID = E.fFPID
)
SELECT * FROM CTE;
4. 查找一个节点的直属父节点（父亲）。 如果查找的是节点的直属父节点，也是不用用到树型查询的。
SELECT B.* FROM tb_Tree A JOIN tb_Tree B ON A.fFPID = B.fPID WHERE A.fPID = 'E00062';
5. 查找一个节点的所有直属父节点（祖宗）。
SELECT * FROM tb_Tree START WITH fPID = 'E00062' CONNECT BY PRIOR fFPID = fPID;
fPID fFPID fArea fLayer fMID fDate fStatus
-----------------------------------------------------------
1 E00062 E00030 B 6 M00001 2017-07-01 0
2 E00030 E00014 B 5 M00001 2017-07-01 0
3 E00014 E00006 B 4 M00001 2017-07-01 0
4 E00006 E00002 B 3 M00001 2017-07-01 0
5 E00002 E00000 B 2 M00001 2017-07-01 0
6 E00000 ROOT 0 1 M00001 2017-07-01 0
这里查找的就是fPID为'E00062'的所有直属父节点，打个比方就是找到一个人的父亲、祖父等。但是值得注意的是这个查询出来的结果的顺序是先列出子类节点再列出父类节点，姑且认为是个倒序吧。
下面这个语句和上面的语句是具有相同的功能，都是查询E00002的所有父节点的：
WITH CTE ( fPID, fFPID, lev) AS
(
SELECT fPID, fFPID, 1 AS lev FROM tb_Tree WHERE fPID = 'E00002'
UNION ALL
SELECT E.fPID, E.fFPID, lev + 1 FROM CTE T, tb_Tree E
WHERE T.fFPID = E.fPID
)
SELECT * FROM CTE;
上面列出两个树型查询方式，第3条语句和第5条语句，这两条语句之间的区别在于prior关键字的位置不同，所以决定了查询的方式不同。 当fFPID = PRIOR ID时，数据库会根据当前的ID迭代出fFPID与该ID相同的记录，所以查询的结果是迭代出了所有的子类记录；而PRIOR ID = fFPID时，数据库会跟据当前的fFPID来迭代出与当前的fFPID相同的id的记录，所以查询出来的结果就是所有的父类结果。
以下是一系列针对树结构的更深层次的查询，这里的查询不一定是最优的查询方式，或许只是其中的一种实现而已。
6. 查询一个节点的兄弟节点（亲兄弟）。
SELECT A.*
FROM tb_Tree A
WHERE EXISTS (SELECT *
FROM tb_Tree B
WHERE A.fFPID = B.fFPID AND B.fPID = 'E00033');
fPID fFPID fArea fLayer fMID fDate fStatus
------------------------------------------------------------
1 E00033 E00016 A 6 M00001 2017-07-01 0
2 E00034 E00016 B 6 M00001 2017-07-01 0
------------------------------------------------------------
这里查询的就是与fPID为'E00033'的节点同属一个父节点的节点了，就好比亲兄弟了。
7. 查询与一个节点同级的节点（族兄弟）。 如果在表中设置了级别的字段，上表中的 fLayer，那么在做这类查询时会很轻松，同一级别的就是与那个节点同级的，在这里列出不使用该字段时的实现!
WITH tmp AS
(SELECT A.*, LEVEL lev
FROM tb_Tree A
START WITH A.fFPID = 'ROOT'
CONNECT BY A.fFPID = PRIOR A.fPID)
SELECT *
FROM tmp
WHERE lev = (SELECT lev
FROM tmp
WHERE fPID = 'E00024')
这里使用两个技巧，一个是使用了LEVEL来标识每个节点在表中的级别，还有就是使用 With 语法模拟出了一张带有级别的临时表。
8. 查询一个节点的父节点的的兄弟节点（伯父与叔父）。
WITH tmp AS
(SELECT tb_Tree.*, LEVEL lev
FROM tb_Tree
START WITH fFPID = 'ROOT'
CONNECT BY fFPID = PRIOR fPID)
SELECT B.*
FROM tmp B,
(SELECT *
FROM tmp
WHERE fPID = 'E00024' AND lev = 2) A
WHERE B.lev = 1
UNION ALL
SELECT *
FROM tmp
WHERE fFPID = (SELECT DISTINCT x.fPID
FROM tmp x,tmp y,
(SELECT *
FROM tmp
WHERE fPID = 'E00024' AND lev > 2) z
WHERE y.fPID = z.fFPID AND x.fPID = y.fFPID);
fPID fFPID fArea fLayer fMID fDate fStatus LEV
-------------------------------------------------------------------
1 E00011 E00005 A 4 M00001 2017-07-01 0 4
2 E00012 E00005 B 4 M00001 2017-07-01 0 4
-------------------------------------------------------------------
这里查询分成以下几步。首先，将第7个一样，将全表都使用临时表加上级别；其次，根据级别来判断有几种类型，以上文中举的例子来说，有三种情况：
（1）当前节点为顶级节点，即查询出来的lev值为1，那么它没有上级节点，不予考虑。
（2）当前节点为2级节点，查询出来的lev值为2，那么就只要保证lev级别为1的就是其上级节点的兄弟节点。
（3）其它情况就是3以及以上级别，那么就要选查询出来其上级的上级节点（祖父），再来判断祖父的下级节点都是属于该节点的上级节点的兄弟节点。 最后，就是使用UNION将查询出来的结果进行结合起来，形成结果集。
9. 查询一个节点的父节点的同级节点（族叔）。
这个其实跟第7种情况是相同的。
WITH tmp AS
(SELECT A.*, LEVEL lev
FROM tb_Tree A
START WITH A.fFPID = 'ROOT'
CONNECT BY A.fFPID = PRIOR A.fPID)
SELECT *
FROM tmp
WHERE lev = (SELECT lev
FROM tmp
WHERE fPID = 'E00024' ) - 1
fPID fFPID fArea fLayer fMID fDate fStatus LEV
-------------------------------------------------------------------
1 E00007 E00003 A 4 M00001 2017-07-01 0 4
2 E00008 E00003 B 4 M00001 2017-07-01 0 4
3 E00009 E00004 A 4 M00001 2017-07-01 0 4
4 E00010 E00004 B 4 M00001 2017-07-01 0 4
5 E00011 E00005 A 4 M00001 2017-07-01 0 4
6 E00012 E00005 B 4 M00001 2017-07-01 0 4
7 E00013 E00006 A 4 M00001 2017-07-01 0 4
8 E00014 E00006 B 4 M00001 2017-07-01 0 4
-------------------------------------------------------------------
只需要做个级别判断就成了。
基本上，常见的查询在里面了，不常见的也有部分了。其中，查询的内容都是节点的基本信息，都是数据表中的基本字段，但是在树查询中还有些特殊需求，是对查询数据进行了处理的，常见的包括列出树路径等。
补充一个概念，对于数据库来说，根节点并不一定是在数据库中设计的顶级节点，对于数据库来说，根节点就是 start with 开始的地方。
下面列出的是一些与树相关的特殊需求。
10. 名称要列出名称全部路径。
这里常见的有两种情况，一种是是从顶级列出，直到当前节点的名称（或者其它属性）；一种是从当前节点列出，直到顶级节点的名称（或其它属性）。
举地址为例：国内的习惯是从省开始、到市、到县、到居委会的，而国外的习惯正好相反（老师说的，还没接过国外的邮件，谁能寄个瞅瞅 ）。
从顶部开始：
SELECT SYS_CONNECT_BY_PATH (fFPID, '/')
FROM tb_Tree
WHERE fPID = 'E00024'
START WITH fFPID = 'ROOT'
CONNECT BY fFPID = PRIOR fPID;
得到胡结果为
/ROOT/E00000/E00002/E00005/E00011
从当前节点开始：
SELECT SYS_CONNECT_BY_PATH (fFPID, '/')
FROM tb_Tree
START WITH fPID = 'E00024'
CONNECT BY PRIOR fFPID = fPID;
路径
----------------------------------------------------
1 /E00011
2 /E00011/E00005
3 /E00011/E00005/E00002
4 /E00011/E00005/E00002/E00000
5 /E00011/E00005/E00002/E00000/ROOT
----------------------------------------------------
在这里我又不得不放个牢骚了。oracle 只提供了一个sys_connect_by_path函数，却忘了字符串的连接的顺序。在上面的例子中，第一个SQL是从根节点开始遍历，而第二个SQL是直接找到当前节点，从效率上来说已经是千差万别，更关键的是第一个SQL只能选择一个节点，而第二个SQL却是遍历出了一颗树来。
sys_connect_by_path 函数就是从 start with 开始的地方开始遍历，并记下其遍历到的节点， start with 开始的地方被视为根节点，将遍历到的路径根据函数中的分隔符，组成一个新的字符串，这个功能还是很强大的。
11. 列出当前节点的根节点。
在前面说过，根节点就是start with开始的地方。
SELECT CONNECT_BY_ROOT fFPID, tb_Tree.*
FROM tb_Tree
START WITH fPID = 'E00024'
CONNECT BY PRIOR fFPID = fPID;
CONNECT_BY_ROOT fPID fFPID fArea fLayer fMID fDate fStatus
----------------------------------------------------------------------------
1 E00011 E00024 E00011 B 5 M00001 2017-07-01 0
2 E00011 E00011 E00005 A 4 M00001 2017-07-01 0
3 E00011 E00005 E00002 A 3 M00001 2017-07-01 0
4 E00011 E00002 E00000 B 2 M00001 2017-07-01 0
5 E00011 E00000 ROOT 0 1 M00001 2017-07-01 0
---------------------------------------------------------------------------
connect_by_root 函数用来列的前面，记录的是当前节点的根节点的内容。
12. 列出当前节点是否为叶子。
这个比较常见，尤其在动态目录中，在查出的内容是否还有下级节点时，这个函数是很适用的。
SELECT CONNECT_BY_ISLEAF, tb_Tree.*
FROM tb_Tree
START WITH fFPID = 'E00010'
CONNECT BY fFPID = PRIOR fPID;
CONNECT_BY_ROOT fPID fFPID fArea fLayer fMID fDate fStatus
----------------------------------------------------------------------------
1 0 E00021 E00010 A 5 M00001 2017-07-01 0
2 1 E00043 E00021 A 6 M00001 2017-07-01 0
3 1 E00044 E00021 B 6 M00001 2017-07-01 0
4 0 E00022 E00010 B 5 M00001 2017-07-01 0
5 1 E00045 E00022 A 6 M00001 2017-07-01 0
6 1 E00046 E00022 B 6 M00001 2017-07-01 0
----------------------------------------------------------------------------
connect_by_isleaf 函数用来判断当前节点是否包含下级节点，如果包含的话，说明不是叶子节点，这里返回 0；反之，如果不包含下级节点，这里返回1。
扫一扫
1115
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
