Oracle层次化查询

一:层次化数据(树)

数据展示:

二:递归查询

2.1 查询根树

SELECT * FROM tree
START WITH pid = 0         --表示根节点,可以指定其他树节点就可以只查询指定树
CONNECT BY PRIOR ID = pid; --PRIOR 表示上一条数据,表示上一条数据的id = 这条数据的pid

2.2查村指定树

SELECT * FROM tree
START WITH ID = 2    --只查询A节点的所有子节点
CONNECT BY PRIOR ID = pid;

2.3查询多个树

SELECT * FROM tree
START WITH NAME IN('A','B')
CONNECT BY PRIOR ID = pid;

三:数据的层次

3.1使用伪劣LEVAL显示节点的层次

SELECT LEVEL, tree.* FROM tree
START WITH pid = 0
CONNECT BY PRIOR ID = pid ORDER BY LEVEL,pid;

结果:

3.2查询数的层次数,该表一共多少个层次

SELECT COUNT(DISTINCT LEVEL) FROM tree
START WITH pid = 0
CONNECT BY PRIOR ID = pid;
--结果: 3

3.3使用LPAD函数进行层次显示

SELECT LEVEL,LPAD(' ',3*LEVEL-1)||NAME AS NAME FROM tree
START WITH pid = 0
CONNECT BY PRIOR ID = pid ;

结果:

标题四:自下而上遍历树

把 connect by prior 中的 id和pid交换顺序 , 同时start with 的id/pid的取值尽量大

SELECT LEVEL,concat(LPAD(' ',2*LEVEL),NAME) FROM tree
START WITH id = 6
CONNECT BY PRIOR pid = ID;

结果: