oracle中的listagg函数

listagg()用于字符串聚集

LISTAGG(XXX--要合并的列,XXX--分隔符) WITHIN GROUP( ORDER BY XXX) over(partition by XXX)


需要注意的事项如下:
1. 必须得分组,也就是说group  by是必须的。

2. listagg函数的第一个参数是需要进行合并的字段;第二个参数是分隔符;同时还需要进行排序和分组within group (order by XXX)

eg:

(1)LISTAGG(XXX--要合并的列,XXX--分隔符) WITHIN GROUP( ORDER BY XXX)

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 
China  Beijing,Guangzhou,Shanghai
Japan  Tokyo
USA  Bostom,New York



(2)LISTAGG(XXX--要合并的列,XXX--分隔符) WITHIN GROUP( ORDER BY XXX) over(partition by XXX)
with temp as(  
  select 500 population, 'China' nation ,'Guangzhou' city from dual union all  
  select 1500 population, 'China' nation ,'Shanghai' city from dual union all  
  select 500 population, 'China' nation ,'Beijing' city from dual union all  
  select 1000 population, 'USA' nation ,'New York' city from dual union all  
  select 500 population, 'USA' nation ,'Bostom' city from dual union all  
  select 500 population, 'Japan' nation ,'Tokyo' city from dual   
)  
select population,  
       nation,  
       city,  
 listagg(city,',') within GROUP (order by city) over (partition by nation) rank  
from temp ;
500	China	Beijing	Beijing,Guangzhou,Shanghai
500	China	Guangzhou	Beijing,Guangzhou,Shanghai
1500	China	Shanghai	Beijing,Guangzhou,Shanghai
500	Japan	Tokyo	Tokyo
500	USA	Bostom	Bostom,New York
1000	USA	New York	Bostom,New York



展开阅读全文

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