一个sql

renfengjun
博客专家认证
2011-09-26 05:23:16
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_

要求数据项:user_ids talks
例如: 'dex and ice' 'hello(换行符)hi do u have gf ? (换行符)no , waiting 4 u '
'dex and rose' 'hello what a beautiful day(换行符) do u have any time to come with me(换行符) yes where r we going to(换行符)up to u '
不要使用where+union
不要纠结我的英文
不要改变我的需求
只想知道可否实现
...全文
94 3 打赏 收藏 转发到动态 举报
写回复
用AI写文章
3 条回复
切换为时间正序
请发表友善的回复…
发表回复
renfengjun 2011-09-27
  • 打赏
  • 举报
回复

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_)

改了一下,多谢哈哈,又学到一个新的函数。
xiaobn_cn 2011-09-26
  • 打赏
  • 举报
回复
由于sys_connect_by_path参数中不允许使用chr(13),所以使用了'//',然后使用chr(13)替换了'//'。这里的'//'可以使用其它标识符替代。
xiaobn_cn 2011-09-26
  • 打赏
  • 举报
回复

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;

17,078

社区成员

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧