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 ;

500ChinaBeijingBeijing,Guangzhou,Shanghai

500ChinaGuangzhouBeijing,Guangzhou,Shanghai

1500ChinaShanghaiBeijing,Guangzhou,Shanghai

500JapanTokyoTokyo

500USABostomBostom,New York

1000USANew YorkBostom,New York