树结构和它的专用函数

原创 2007年10月10日 19:56:00

树结构和它的专用函数SYS_CONNECT_BY_PATH

简单的树型结构
关于树的普通应用
学习了下这个函数, 用ORGINDUSTRIES的表做了个测试:
正常的树型结构
select lpad(' ',6*(level-1))||industry,indlevel,indid,pindid
from ORGINDUSTRIES
start with indid=1
connect by pindid=prior indid
结果显示如下
                 Indlevel  indid    pindid
        服装与服饰               1             1             0
              服装               2             2               1
                    女装        3             3               2

倒型树
下面这个例子是个”倒数”—倒过来的树型结构
select lpad(' ',6*(level-1))||industry,indlevel,indid,pindid
from ORGINDUSTRIES
start with indid=20
connect by indid=prior pindid;
这是标准结果:
                             Indlevel indid    pindid
二手服装                      3        20       2
      服装                    2        2        1
            服装与服饰        1        1        0
结论
无论正树还是倒树, 关键就在于connect by的条件.
正树:  必须是  ‘父’= prior ‘子’
倒树:  必须是  ‘子’= prior ‘父’

树型结构的条件过滤
采用树型结构的话, 如果我们想将树上的一个分支砍掉.  将分支后面的结构都抛弃掉, 这个可以实现麽?当然可以。 但是不是用where, where条件只能去除单一的条件。
所以, 这种树型的过滤条件就需要加在connect by上面。

测试如下:由于用真实环境比较贴近实际,所以提前用下SYS_CONNECT_BY_PATH函数来显示下环境

不加任何条件的环境:
select areaname,sys_connect_by_path(areaname,',')
from areas bb
start with areaname='中国大陆'
connect by parentareaid=prior areaid  

结果:
1        中国大陆,中国大陆
2        北京        ,中国大陆,北京
3        北京        ,中国大陆,北京,北京
4        东城区        ,中国大陆,北京,东城区
5        西城区        ,中国大陆,北京,西城区
22        广东        ,中国大陆,广东
23        广州        ,中国大陆,广东,广州
24        汕尾        ,中国大陆,广东,汕尾
25        潮阳        ,中国大陆,广东,潮阳
46        上海        ,中国大陆,上海
47        上海        ,中国大陆,上海,上海
48        黄浦区        ,中国大陆,上海,黄浦区
49        闸北区        ,中国大陆,上海,闸北区


加了where过滤条件的SQL:
select areaname,sys_connect_by_path(areaname,',')
from areas bb
where bb.areaid>861000
start with areaname='中国大陆'
connect by parentareaid=prior areaid

结果为:
2        北京        ,中国大陆,北京
3        北京        ,中国大陆,北京,北京
4        东城区        ,中国大陆,北京,东城区
5        西城区        ,中国大陆,北京,西城区
22        广东        ,中国大陆,广东
23        广州        ,中国大陆,广东,广州
24        汕尾        ,中国大陆,广东,汕尾
25        潮阳        ,中国大陆,广东,潮阳
46        上海        ,中国大陆,上海
47        上海        ,中国大陆,上海,上海
48        黄浦区        ,中国大陆,上海,黄浦区
49        闸北区        ,中国大陆,上海,闸北区

结论:去掉了“1        中国大陆,中国大陆”数据

加了connect by的过滤条件:
select areaname,sys_connect_by_path(areaname,',')
from areas bb
where bb.areaid>861000
start with areaname='中国大陆'
connect by parentareaid=prior areaid  and areaname<>'广东'

结果为:
2        北京        ,中国大陆,北京
3        北京        ,中国大陆,北京,北京
4        东城区        ,中国大陆,北京,东城区
5        西城区        ,中国大陆,北京,西城区
46        上海        ,中国大陆,上海
47        上海        ,中国大陆,上海,上海
48        黄浦区        ,中国大陆,上海,黄浦区
49        闸北区        ,中国大陆,上海,闸北区

结论:去掉了整个广东的分支,  在结果集中只有北京和上海


SYS_CONNECT_BY_PATH函数
采用SYS_CONNECT_BY_PATH函数为:

select industry,sys_connect_by_path(industry,'/')
from ORGINDUSTRIES
start with indid=3
connect by indid=prior pindid;

结果为:
女装               /女装
服装               /女装/服装
服装与服饰            /女装/服装/服装与服饰

这样的话, 就可以实现, 树结构的结果集的单行拼接:

我们只需要取最大的字段就OK了

测试如下:

select max(sys_connect_by_path(industry,'/'))
from ORGINDUSTRIES
start with indid=3
connect by indid=prior pindid;

结果为:
/女装/服装/服装与服饰


复杂的树型结构――多列变单列
树型结构也分单树和多树(我的称呼,实际上就是指单支和多支)
对于下面的这种情况, 我们必须要构造的树就属于单支树。
原始环境
环境如下:
select * from test;

结果为:
1        n1
1        n2
1        n3
1        n4
1        n5
3        t1
3        t2
3        t3
3        t4
3        t5
3        t6
2        m1

造树
脚本如下:
select no,q,
       no+row_number() over( order by no) rn,
       row_number() over(partition by no order by no) rn1
from test

结果如下:
No  Q  RN RN1
1        n1        2        1
1        n2        3        2
1        n3        4        3
1        n4        5        4
1        n5        6        5
2        m1        8        1
3        t1        10        1
3        t2        11        2
3        t3        12        3
3        t4        13        4
3        t5        14        5
3        t6        15        6

每列的目的是:
RN1列主要的目的是分组, 按照value值‘1’,我们可以start with使用它。

RN列主要用来做connect by使用。 实际上它就是我们要的树。
第一个支: 2,3,4,5,6
第二个支: 8
第三个支: 10,11,12,13,14,15

中间为什么要断掉:7,9  目的就是为了区别每个分支。 到后面看具体的SQL,就明白这里的说法了。

杀手锏
既然我们有了树, 就可以使用树型函数SYS_CONNECT_BY_PATH和connect by啦,来拼接我们所需要的多列值。

脚本如下:
select no,sys_connect_by_path(q,',')
from (
select no,q,
       no+row_number() over( order by no) rn,
       row_number() over(partition by no order by no) rn1
from test
)
start with rn1=1
connect by rn-1=prior rn

结果为:
1        ,n1
1        ,n1,n2
1        ,n1,n2,n3
1        ,n1,n2,n3,n4
1        ,n1,n2,n3,n4,n5
2        ,m1
3        ,t1
3        ,t1,t2
3        ,t1,t2,t3
3        ,t1,t2,t3,t4
3        ,t1,t2,t3,t4,t5
3        ,t1,t2,t3,t4,t5,t6

终极武器
最终我们要的值,是单列值, 其实想想, 也就是最长的一行咯。 那么就好办了。 我们直接GROUP BY ,然后取MAX值。
脚本如下:
select no,max(sys_connect_by_path(q,','))
from (
select no,q,
       no+row_number() over( order by no) rn,
       row_number() over(partition by no order by no) rn1
from test
)
start with rn1=1
connect by rn-1=prior rn
group by no

结果为:
1        ,n1,n2,n3,n4,n5
2        ,m1
3        ,t1,t2,t3,t4,t5,t6

如果觉得前面的‘,’不好看,可以使用ltrim去掉。 或者用substr也可以。
如下:
ltrim(max(sys_connect_by_path(q,',')),',')
或者
substr(max(sys_connect_by_path(q,',')),2) 
 

树结构和它的专用函数SYS_CONNECT_BY_PATH

简单的树型结构 关于树的普通应用 学习了下这个函数, 用ORGINDUSTRIES的表做了个测试: 正常的树型结构 select lpad(' ',6*(level-1))||industry,ind...
  • a416090287
  • a416090287
  • 2013年07月03日 22:37
  • 1122

专用网络对外开发!阿里云ECS!深坑!

阿里云现在没有之前的经典网络了,要买的话我这边好像只能选专用网络。。然而视频还是教的经典网络。根本没有外网入 ,外网出这个端口。。 小白都比我白。。。觉得被视频诱拐了。。下面转一下如何解决的!实测有效...
  • qq_33951440
  • qq_33951440
  • 2017年07月29日 21:14
  • 762

线程特定数据TSD及其实现原理

引言: 单线程C程序有两类基本数据:局部数据和全局数据。对于多线程C程序,添加了第三类数据:线程特定数据 那么为什么要引入线程特定数据呢?试想如果你的一个线程里面嵌套调用了很多函数,而你又想在这些函数...
  • mengxingyuanlove
  • mengxingyuanlove
  • 2016年03月04日 14:44
  • 1084

虚函数和它的虚表

一、多态与虚函数(virtual) 先理清楚下边三句话: 1.函数重载是处理同一层次上的同名函数问题,而虚函数处理的是不同派生层次上的同名函数问题。(覆盖) 2.同名隐藏则是基类与派生类同名函数...
  • silent__dh
  • silent__dh
  • 2016年11月06日 13:26
  • 356

【29】求无序序列中最小k个数

题目:给定一个x
  • cgl1079743846
  • cgl1079743846
  • 2014年06月12日 18:58
  • 1135

VPN(虚拟专用网络)

一、VPN工作原理- -简介   VPN,全称为Virtual Private Network,译为无线虚拟专用网络,主要用于在公用网络上建立专用网络,进行加密通讯。这样的表述可能有点抽象,我们举个...
  • u014421556
  • u014421556
  • 2016年07月12日 13:50
  • 1637

Numpy(五)专用函数

1、排序和搜索 NumPy提供了多种排序函数,如下所示: sort函数返回排序后的数组; lexsort函数根据键值的字典序进行排序; argsort函数返回输入数组排序后的下标; nd...
  • m0_37264397
  • m0_37264397
  • 2018年01月08日 21:43
  • 31

领域专用语言(DSL)简介

原文地址:http://blog.csdn.net/u010278882/article/details/50554299 主要回答几下几个问题  1 什么是 DSL  2 为什么使用 DSL...
  • tanga842428
  • tanga842428
  • 2017年02月17日 08:49
  • 2899

python 专用方法

高级专用类方法 除了 __getitem__ 和 __setitem__ 之外 Python 还有更多的专用函数。某些可以让你模拟出你甚至可能不知道的功能。 下面的例子...
  • screaming
  • screaming
  • 2016年07月11日 20:34
  • 734

php不用递归实现无限分类数据的树形格式化

/**  * 此方法由@Tonton 提供  * http://my.oschina.net/u/918697  * @date 2012-12-12   */
  • baikeliang
  • baikeliang
  • 2016年04月15日 17:22
  • 1155
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:树结构和它的专用函数
举报原因:
原因补充:

(最多只允许输入30个字)