快捷導(dǎo)航

oracle 樹(shù)查詢(xún) 語(yǔ)句

更新時(shí)間：2009年07月17日 01:06:31 作者：

oracle 樹(shù)查詢(xún)，需要的朋友可以參考下，代碼有點(diǎn)亂不好意思啊

格式：
SELECT column
FROM table_name
START WITH column=value
CONNECT BY PRIOR 父主鍵=子外鍵
select lpad(' ',4*(level-1))||name name,job,id,super from emp
start with super is null
connect by prior id=super
例子：
原始數(shù)據(jù)：select no,q from a_example2
NO NAME
---------- ------------------------------
001 a01
001 a02
001 a03
001 a04
001 a05
002 b01
003 c01
003 c02
004 d01
005 e01
005 e02
005 e03
005 e04
005 e05
需要實(shí)現(xiàn)得到結(jié)果是：
001 a01;a02;a03
002 b01
003 c01;c02
004 d01
005 e01;e02;e03;e04;e05
思路：
1、ORACLE8.1之后有個(gè)connect by 子句，取出整棵樹(shù)數(shù)據(jù)。
create table a_example1
(
no char(3) not null,
name varchar2(10) not null,
parent char(3)
)
insert into a_example1
values('001','老王',null)
insert into a_example1
values('101','老李',null)
insert into a_example1
values('002','大王1','001')
insert into a_example1
values('102','大李1','101')
insert into a_example1
values('003','大王2','001')
insert into a_example1
values('103','大李2','101')
insert into a_example1
values('003','小王1','002')
insert into a_example1
values('103','小李1','102')
ＮＯ　?。危粒停拧。校粒遥牛危?
001 老王
101 老李
002 大王1 001
102 大李1 101
003 大王2 001
103 大李2 101
003 小王1 002
103 小李1 102
//按照家族樹(shù)取數(shù)據(jù)
select *　from a_example1
select level,sys_connect_by_path(name,'/') path
from a_example1
start with /*name = '老王' and*/ parent is null
connect by parent = prior no
結(jié)果：
1 /老王
2 /老王/大王1
3 /老王/大王1/小王1
2 /老王/大王2
1 /老李
2 /老李/大李1
3 /老李/大李1/小李1
2 /老李/大李2
按照上面思路，我們只要將原始數(shù)據(jù)做成如下結(jié)構(gòu)：
NO NAME
001 a01
001 a01/a02
001 a01/a02/a03
001 a01/a02/a03/a04
001 a01/a02/a03/a04/a05
002 b01
003 c01
003 c01/c02
004 d01
005 e01
005 e01/e02
005 e01/e02/e03
005 e01/e02/e03/e04
005 e01/e02/e03/e04/e05
最后按NO分組，取最大的一個(gè)值即為所需的結(jié)果。
NO NAME
001 a01/a02/a03/a04/a05
002 b01
003 c01/c02
004 d01
005 e01/e02/e03/e04/e05
SQL語(yǔ)句：
select no,max(sys_connect_by_path(name,';')) result from
(select no,name,rn,lead(rn) over(partition by no order by rn) rn1
from (select no,name,row_number() over(order by no,name desc) rn from a_example2)
)
start with rn1 is null connect by rn1 = prior rn
group by no
語(yǔ)句分析：
1、 select no,name,row_number() over(order by no,name desc) rn from a_example2
按照NO升序排序，同時(shí)按照NAME降序排序，產(chǎn)生偽列，目的是要形成樹(shù)結(jié)構(gòu)
ＮＯ　　ＮＡＭＥ?。遥?
001 a03 1
001 a02 2
001 a01 3
002 b01 4
003 c02 5
003 c01 6
004 d01 7
005 e05 8
005 e04 9
005 e03 10
005 e02 11
005 e01 12
2、select no,name,rn,lead(rn) over(partition by no order by rn) rn1
from ( select no,name,row_number() over(order by no,name desc) rn from a_example2)
生成家族譜，即子節(jié)點(diǎn)與父節(jié)點(diǎn)有對(duì)應(yīng)關(guān)系，對(duì)應(yīng)關(guān)系通過(guò)rn和 rn1。其中l(wèi)ead為上一條記錄的RN值
ＮＯ　?。危粒停拧。遥巍　。遥危薄?001 a03 1 2 --
說(shuō)明：針對(duì)ＮＯ＝００１來(lái)說(shuō)，其下一條記錄的ＲＮ＝２ 001 a02 2 3　--說(shuō)明：針對(duì)ＮＯ＝００１來(lái)說(shuō)，其下一條記錄的ＲＮ＝３ 001 a01 3 　--說(shuō)明：針對(duì)ＮＯ＝００１來(lái)說(shuō)，其下一條記錄的RN IS NULL
002 b01 4 003 c02 5 6 003 c01 6 004 d01 7 005 e05 8 9 005 e04 9 10 005 e03 10 11 005 e02 11 12 005 e01 12
3、select no,sys_connect_by_path(name,';') result from
(select no,name,rn,lead(rn) over(partition by no order by rn) rn1
from ( select no,name,row_number() over(order by no,name desc) rn from a_example2))
start with rn1 is null connect by rn1 = prior rn
正式生成樹(shù)
ＮＯ　　　ＲＥＳＵＬＴ
001 ;a01
001 ;a01;a02
001 ;a01;a02;a03
002 ;b01
005 ;e01
005 ;e01;e02
005 ;e01;e02;e03
005 ;e01;e02;e03;e04
005 ;e01;e02;e03;e04;e05
003 ;c01
003 ;c01;c02
004 ;d01
將上面結(jié)果按照NO分組，取result最大值即可，所以將上述語(yǔ)句改為
select no,max(sys_connect_by_path(name,';')) result from
(select no,name,rn,lead(rn) over(partition by no order by rn) rn1
from (select no,name,row_number() over(order by no,name desc) rn from a_example2)
)
start with rn1 is null connect by rn1 = prior rn
group by no
得到所需結(jié)果。