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