sys_connect_by_path使用举例

create table A
(
ID CHAR(1) not null,
NAME VARCHAR2(150)
);
insert into A (ID, NAME)
values ('1', 'seaman');
insert into A (ID, NAME)
values ('2', 'sea');
insert into A (ID, NAME)
values ('3', 'man');

create table B
(
ID CHAR(1) not null,
DESCI VARCHAR2(150)
);

insert into B (ID, DESCI)
values ('1', 'desc11');
insert into B (ID, DESCI)
values ('2', 'desc21');
insert into B (ID, DESCI)
values ('2', 'desc22');
insert into B (ID, DESCI)
values ('2', 'desc23');
insert into B (ID, DESCI)
values ('3', 'desc31');
insert into B (ID, DESCI)
values ('3', 'desc33');
commit;

实现如下查询结果
seaman desc11
sea desc21-desc22-desc23
man desc31-desc33

方法如下：

select name,
substr(max(sys_connect_by_path(desci,
'-')),
2) ss
from (select a.id,
a.name,
b.desci,
row_number() over(partition by name order by desci) num
from A,
B
where a.id = b.id)
start with num = 1
connect by prior name = name
and prior num = num - 1
group by name
;

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/594892/viewspace-919949/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/594892/viewspace-919949/