17,078
社区成员
发帖
与我相关
我的任务
分享
with talks as (
select 'hello' as talk ,'dex' as from_ ,'ice' as to_ , 1 no_ from dual
union
select 'hi' as talk ,'ice' as from_ ,'dex' as to_ , 2 no_ from dual
union
select 'do u have gf ? ' as talk ,'ice' as from_ ,'dex' as to_ , 3 no_ from dual
union
select 'no , waiting 4 u ' as talk ,'ice' as from_ ,'dex' as to_ , 4 no_ from dual
union
select 'hello what a beautiful day ' as talk ,'dex' as from_ ,'rose' as to_ , 1 no_ from dual
union
select 'do u have any time to come with me ' as talk ,'dex' as from_ ,'rose' as to_ , 2 no_ from dual
union
select 'yes where r we going to ' as talk ,'rose' as from_ ,'dex' as to_ , 3 no_ from dual
union
select ' up to u ' as talk ,'dex' as from_ ,'rose' as to_ , 4 no_ from dual
)
select from_||' to '||to_ , listagg(talk,chr(10)) within group(order by no_) from talks group by from_||' to '||to_
with talks as (
select 'hello' as talk ,'dex' as from_ ,'ice' as to_ , 1 no_ from dual
union
select 'hi' as talk ,'ice' as from_ ,'dex' as to_ , 2 no_ from dual
union
select 'do u have gf ? ' as talk ,'ice' as from_ ,'dex' as to_ , 3 no_ from dual
union
select 'no , waiting 4 u ' as talk ,'ice' as from_ ,'dex' as to_ , 4 no_ from dual
union
select 'hello what a beautiful day ' as talk ,'dex' as from_ ,'rose' as to_ , 1 no_ from dual
union
select 'do u have any time to come with me ' as talk ,'dex' as from_ ,'rose' as to_ , 2 no_ from dual
union
select 'yes where r we going to ' as talk ,'rose' as from_ ,'dex' as to_ , 3 no_ from dual
union
select ' up to u ' as talk ,'dex' as from_ ,'rose' as to_ , 4 no_ from dual
)
select greatest(from_,to_)||' to '||least(from_,to_),listagg(talk,chr(10)) within group(order by no_) from talks group by greatest(from_,to_)||' to '||least(from_,to_)
with talks as (
select 'hello' as talk ,'dex' as from_ ,'ice' as to_ , 1 no_ from dual
union
select 'hi' as talk ,'ice' as from_ ,'dex' as to_ , 2 no_ from dual
union
select 'do u have gf ? ' as talk ,'ice' as from_ ,'dex' as to_ , 3 no_ from dual
union
select 'no , waiting 4 u ' as talk ,'ice' as from_ ,'dex' as to_ , 4 no_ from dual
union
select 'hello what a beautiful day ' as talk ,'dex' as from_ ,'rose' as to_ , 1 no_ from dual
union
select 'do u have any time to come with me ' as talk ,'dex' as from_ ,'rose' as to_ , 2 no_ from dual
union
select 'yes where r we going to ' as talk ,'rose' as from_ ,'dex' as to_ , 3 no_ from dual
union
select ' up to u ' as talk ,'dex' as from_ ,'rose' as to_ , 4 no_ from dual
)
select u1||' and '||u2||replace(sys_connect_by_path(talk,'//'),'//',chr(13)) from (select talk,greatest(from_,to_) U1,least(to_,from_) U2,no_ from talks)
where CONNECT_BY_ISLEAF = 1
start with no_ = 1
connect by u1 = prior u1 and u2 = prior u2 and no_ = prior no_ + 1;