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

1.数据组成

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

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 结果如下所示: Oracle树形结构数据-connect by ,level的使用   注意:若prior关键字缺省:则只能查询到符合条件的起始行,并不进行递归查询;
SELECT  *     FROM QIANCODE.TREE_TABLE_BASIC T   START WITH T.ID='111' CONNECT BY  T.ID=T.PID 结果如下所示: Oracle树形结构数据-connect by ,level的使用

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

SELECT   *      FROM QIANCODE.TREE_TABLE_BASIC T   START WITH T.ID='111' CONNECT BY PRIOR T.PID=T.ID; 结果如下图所示: Oracle树形结构数据-connect by ,level的使用

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; 显式结果如下: Oracle树形结构数据-connect by ,level的使用

注意: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数据库里占多少字节跟数据库的字符集有关,UTF8时,长度为三。

 

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

 

 

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;   查询结果如下: Oracle树形结构数据-connect by ,level的使用   特别说明: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’的节点出现环路)                    Oracle树形结构数据-connect by ,level的使用                 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;                     查询结果如下:                     Oracle树形结构数据-connect by ,level的使用              即可得出环路出现在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');

 

上一篇:cannot connect to 127.0.0.1:7555: 由于目标计算机积极拒绝,无法连接。 (10061),有效解决


下一篇:qt实用小技巧