Oracle：树查询及相关函数

妞妞6086

Oracle树查询的最重要的就是select...start with... connect by ...prior 语法了。依托于该语法，我们可以将一个表形结构的中以树的顺序列出来。
create table SECURITY_RES_CATEGORY
(
ID          NUMBER(6) not null,
NAME        VARCHAR2(100),
DESCRIPTION VARCHAR2(200),
PARENT_ID   NUMBER(6),
APP_ID      NUMBER(3),
STATUS      NUMBER(1) default 1
)

PARENT_ID存储的是上级ID，如果是顶级父节点，该PARENT_ID为0
引用
表中最好别有null记录，这会引起全文扫描，建议使用0代替

1. 查找树中的所有顶级父节点
select * from security_res_category where parent_id=0


2.查找一个节点的直属子节点（所有儿子）
select * from security_res_category where parent_id=361

3.查找一个节点的所有 直属子节点（所有后代）
select * from security_res_category where app_id=181 START WITH ID=361 CONNECT BY parent_id = PRIOR ID;


4.查找一个节点的直属父节点（父亲）
SELECT b.* FROM security_res_category a JOIN security_res_category b ON a.parent_id = b.ID WHERE a.ID = 261;

5.查找一个节点的所有直属父节点（祖宗）
SELECT * FROM security_res_category START WITH ID = 363 CONNECT BY PRIOR parent_id = ID;


这里查找的就是ID为363的所有直属父节点，打个比方就是找到一个人的父亲、祖父等。但是值得注意的是这个查询出来的结果的顺序是先列出子类节点再列出父类节点，姑且认为是个倒序吧。
上面列出两个树型查询方式，第3条语句和第5条语句，这两条语句之间的区别在于prior关键字的位置不同，所以决定了查询的方式不同。
当parent_id=PRIOR ID时，数据库会根据当前的ID迭代出parent_id与该ID相同的记录，所以查询的结果是迭代出了所有的子类记录；而PRIOR ID=parent_id时，数据库会跟据当前的parent_id来迭代出与当前的parent_id相同的id的记录，所以查询出来的结果就是所有的父类结果。
以下是一系列针对树结构的更深层次的查询，这里的查询不一定是最优的查询方式，或许只是其中的一种实现而已。
6.查询一个节点的兄弟节点（亲兄弟）
select * from security_res_category where parent_id=
(select parent_id from security_res_category where id=363)


7.名称要列出名称全部路径
SELECT SYS_CONNECT_BY_PATH (mc, '/')  
FROM security_res_category  
WHERE ID = 6498  
START WITH parent_id=0  
CONNECT BY parent_id = PRIOR ID


11.列出当前节点的根节点
SELECT CONNECT_BY_ROOT name, security_res_category.*  
FROM security_res_category where id=363
START WITH parent_id=0  
CONNECT BY PRIOR id = parent_id;  



12.列出当前节点是否为叶子
SELECT CONNECT_BY_ISLEAF, security_res_category.*  
FROM security_res_category where id=963  
START WITH parent_id=0  
CONNECT BY parent_id = PRIOR ID;