Oracle中将查询出的多条记录的某个字段拼接成一个字符串的方法

http://zhidao.baidu.com/link?url=PQfRn7OH1XlZmCeQNT-Uofy00geAzvSpoDZ-B70rF33_TIcCCkRfCBfvOZtbZOq4V9areNdYt4KvF4oS9unZfyFBScPCKBwGKprlVy7jHWa
with temp as(  
  select 'China' nation ,'Guangzhou' city from dual union all  
  select 'China' nation ,'Shanghai' city from dual union all  
  select 'China' nation ,'Beijing' city from dual union all  
  select 'USA' nation ,'New York' city from dual union all  
  select 'USA' nation ,'Bostom' city from dual union all  
  select 'Japan' nation ,'Tokyo' city from dual   
)  
select nation,listagg(city,',') within GROUP (order by city)  
from temp  
group by nation 

这是最基础的用法:
LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX)
 
用法就像聚合函数一样,通过Group by语句,把每个Group的一个字段,拼接起来。

非常方便

可以考虑使用wmsys.wm_concat方法,该方法是将记录的值使用逗号间隔拼接
使用方法如下:
select t.rank, WMSYS.WM_CONCAT(t.Name) TIME From t_menu_item t GROUP BY t.rank;

select id,wmsys.wm_concat(name) over (order by id) name from idtable;

阅读更多
换一批

没有更多推荐了,返回首页