服务器之家

服务器之家 > 正文

oracle 树查询 语句

时间:2019-11-05 15:16     来源/作者: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') 
NO  NAME PARENT 
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降序排序,产生伪列,目的是要形成树结构 
NO  NAME RN 
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值 
NO  NAME RN  RN1  001 a03 1 2 --
说明:针对NO=001来说,其下一条记录的RN=2 001 a02 2 3 --说明:针对NO=001来说,其下一条记录的RN=3 001 a01 3  --说明:针对NO=001来说,其下一条记录的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 
正式生成树 
NO   RESULT 
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 
得到所需结果。 

标签:

相关文章

热门资讯

玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分
玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分 2019-06-21
男生常说24816是什么意思?女生说13579是什么意思?
男生常说24816是什么意思?女生说13579是什么意思? 2019-09-17
华为nova5pro和p30pro哪个好 华为nova5pro和华为p30pro对比详情
华为nova5pro和p30pro哪个好 华为nova5pro和华为p30pro对比详情 2019-06-22
Nginx服务器究竟是怎么执行PHP项目
Nginx服务器究竟是怎么执行PHP项目 2019-05-24
超A是什么意思 你好a表达的是什么
超A是什么意思 你好a表达的是什么 2019-06-06
返回顶部