Oracle树形结构数据-connect by ,level的使用

2022-09-16 20:20:46

1.数据组成

PS:文末有insert语句，注意最后一列的列名为leval

2.基本查询

2.1.查询某节点及该节点下的所有子孙节点

SELECT * FROM QIANCODE.TREE_TABLE_BASIC T START WITH T.ID='111' CONNECT BY PRIOR T.ID=T.PID 结果如下所示：

注意：若prior关键字缺省：则只能查询到符合条件的起始行，并不进行递归查询；
SELECT * FROM QIANCODE.TREE_TABLE_BASIC T START WITH T.ID='111' CONNECT BY T.ID=T.PID 结果如下所示：

2.查询某节点及该节点上的所有祖先节点

SELECT * FROM QIANCODE.TREE_TABLE_BASIC T START WITH T.ID='111' CONNECT BY PRIOR T.PID=T.ID; 结果如下图所示：

3.按层级展示某节点下的所有子节点(LEVEL的使用；注意where条件放在start with之前，order by 放最后)

/*在树形结构节点很多的情况一下，一般会采用异步刷新的方式进行，在默认加载的情况下，会展开到某个层级。
这种情况下，不但要获取某个节点的祖先节点，还需要获取祖先节点的兄弟节点，在这种情况下可以通过level进行*/
SELECT T.ID , T.PID , T. NAME, LEVEL
FROM QIANCODE.TREE_TABLE_BASIC T
WHERE LEVEL >= 1
START WITH T.ID = '111'
CONNECT BY PRIOR T.ID = T.PID
ORDER BY LEVEL, ID; 显式结果如下：

注意：level显式的是伪列，是按当前查询出来的结果进行层级排序。

所以这里在原数据中层级为4的班主任在当前查询中level为2.

4.显式出树的级别查询

select t.id,LENGTHB(name),4*(leval-1),Lpad(t.name,LENGTHB(name)+4*(leval-1)),
Lpad(t.name,leval*7),RPAD(' ',4*(leval-1)）||name，
t.pid,t.leval from BIANLI t START WITH pID='-99999' CONNECT BY PRIOR ID=PID

4.1.RPAD()和LPAD()函数的使用

RPAD(string,length,[pad_string])、LPAD(string,length,[pad_string])：从左或往右使用指定的字符串pad_string对string进行填充；

pad_string可省略，默认使用空格填充；

length表示字符最终返回的总长度。

如下查询：

select rpad('aabbcc',2,'hh') from dual; --返回 ‘aa’
select rpad('aabbcc',12,'hh') from dual; --返回 ‘aabbcchhhhhh’

4.2.区别函数lengthb(string)和length(string)

lengthb(string)：计算string所占的字节长度：返回字符串的长度，单位是字节。

length(string)：计算string所占的字符长度：返回字符串的长度，单位是字符。

如下查询： SELECT LENGTHB('中国') FROM DUAL; -- 返回 6
SELECT LENGTH('中国') FROM DUAL; -- 返回 2 对于单字节字符,LENGTHB和LENGTH是一样的.可以用length(‘string’)=lengthb(‘string’)判断字符串是否含有中文。
注：一个汉字在Oracle数据库里占多少字节跟数据库的字符集有关，UTF８时，长度为三。

4.3.巧妙利用函数RPAD(),展示更整齐

5.其他常用

SELECT T.ID
,T.NAME
,T.PID
,CONNECT_BY_ISLEAF      LEAF -- 判断是否为叶结点,O否1是
,SYS_CONNECT_BY_PATH(T.NAME,'|') PATH    -- 遍历的路径
,CONNECT_BY_ROOT(T.NAME) ROOT -- 遍历根结点
   --,CONNECT_BY_ISCYCLE      ISCYCLE -- 查询树是否有环路【使用connect_by_iscycle时，必须加上nocycle关键字】
   ,level   LEVELS -- 结点所属树的层数
   FROM QIANCODE.TREE_TABLE_BASIC T
START WITH T.ID='1'
CONNECT BY PRIOR T.ID=T.PID; 查询结果如下：

特别说明：connect_by_iscycle：伪列，验证这个数是否有环
适用情景：验证配置树是否有环，并查出是哪个结点
   1.修改表数据，使得表数据出现环路
update QIANCODE.TREE_TABLE_BASIC T set t.pid='11111' where t.id='1';commit;
2.再执行以上connect_by_root()查询语句报错 ——> ora-01436：“用户数据中的connect by 循环”
出现环路时问题解决如下： 3.1.检查是哪个结点出现问题【使用connect_by_iscycle时，必须加上nocycle关键字】
SELECT    t.id    ,t.name    ,t.pid,connect_by_iscycle
   FROM QIANCODE.TREE_TABLE_BASIC T
   START WITH T.ID='1'
   CONNECT BY nocycle PRIOR t.id=t.pid;    查询结果如下：（向下查出id=‘1111’的节点出现环路）

3.2.根据上面查出的节点id，向上遍历找到问题结点 SELECT t.id ,t.name ,t.pid,connect_by_iscycle
FROM QIANCODE.TREE_TABLE_BASIC T
START WITH T.ID='11111'
CONNECT BY nocycle PRIOR t.pid=t.id; 查询结果如下：

即可得出环路出现在id=‘1’和id=‘1111’首尾两个节点
4.恢复数据
update QIANCODE.TREE_TABLE_BASIC T set t.pid='-99999' where t.id='1';commit; 原文：https://www.cnblogs.com/zhoudaqianhaha/p/9776619.html

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('1', '校长', '-99999', '1');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('11', '副校长1', '1', '2');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('111', '-年级教导主任', '11', '3');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('1111', '一班班主任', '111', '4');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('11111', '一班语文老师', '1111', '5');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('11112', '一班数学老师', '1111', '5');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('1112', '二班班主任', '111', '4');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('11113', '二班语文老师', '1112', '5');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('11114', '二班数学老师', '1112', '5');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('112', '二年级教导主任', '11', '3');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('1113', '三班班主任', '112', '4');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('11115', '三班语文老师', '1113', '5');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('11116', '三班数学老师', '1113', '5');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('1114', '四班班主任', '112', '4');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('11117', '四班语文老师', '1114', '5');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('11118', '四班数学老师', '1114', '5');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('12', '副校长2', '1', '2');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('113', '三年级教导主任', '12', '3');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('1115', '五班班主任', '113', '4');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('11119', '五班语文老师', '1115', '5');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('11120', '五班数学老师', '1115', '5');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('1116', '六班班主任', '113', '4');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('11121', '六班语文老师', '1116', '5');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('11122', '六班数学老师', '1116', '5');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('114', '四年级教导主任', '12', '3');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('1117', '七班班主任', '114', '4');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('11123', '七班语文老师', '1117', '4');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('11124', '七班数学老师', '1117', '5');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('1118', '八班班主任', '114', '4');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('11125', '八班语文老师', '1118', '5');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('11126', '八班数学老师', '1118', '5');

码农公寓