oracle 里有关树查询

格式：
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

 

 

例子：
原始数据：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

需要实现得到结果是：
001    a01;a02;a03
002    b01
003   c01;c02
004   d01
005   e01;e02;e03;e04;e05

思路：
1、ORACLE8.1之后有个connect by 子句，取出整棵树数据。
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

//按照家族树取数据
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
结果：
1   /老王
2   /老王/大王1
3   /老王/大王1/小王1
2   /老王/大王2
1   /老李
2   /老李/大李1
3   /老李/大李1/小李1
2   /老李/大李2

按照上面思路，我们只要将原始数据做成如下结构：
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分组，取最大的一个值即为所需的结果。
NO         NAME
001        a01/a02/a03/a04/a05
002         b01
003         c01/c02
004         d01
005         e01/e02/e03/e04/e05

SQL语句：
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

语句分析：
1、 select no,name,row_number() over(order by no,name desc) rn from a_example2
按照NO升序排序，同时按照NAME降序排序，产生伪列，目的是要形成树结构
ＮＯ　　ＮＡＭＥ　ＲＮ
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)
生成家族谱，即子节点与父节点有对应关系，对应关系通过rn和 rn1。其中lead为上一条记录的RN值

ＮＯ　　ＮＡＭＥ　ＲＮ　　ＲＮ１　                                                       001        a03     1        2   --说明：针对ＮＯ＝００１来说，其下一条记录的ＲＮ＝２     001        a02     2        3　--说明：针对ＮＯ＝００１来说，其下一条记录的ＲＮ＝３      001        a01     3        　--说明：针对ＮＯ＝００１来说，其下一条记录的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
正式生成树  
ＮＯ　　　ＲＥＳＵＬＴ
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

将上面结果按照NO分组，取result最大值即可，所以将上述语句改为
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
得到所需结果。