数据库树结构 oracle的专用方法
树结构和它的专用函数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)
关于树的普通应用
学习了下这个函数, 用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)