Oracle ListAgg 和 wm_concat函数

ListAgg  wmsys.wm_concat 都是文字连接函数，可以将某列结果链接成一个字符串

ListAgg是Oracle 11g Release2新加入的功能。

create table strAggSample(ID,Val) as
select 111,'a' from dual union all
select 111,'b' from dual union all
select 111,'c' from dual union all
select 222,'d' from dual union all
select 222,'e' from dual union all
select 222,'f' from dual;

select ID,Val,
wmsys.wm_concat(Val) over(partition by ID order by Val desc) as strAgg1,
wmsys.wm_concat(Val) over(order by Val) as strAgg2,
ListAgg(Val,',') withIn group(order by Val) over() as strAgg3,
ListAgg(Val,',') withIn group(order by Val) over(partition by ID) as strAgg4
  from strAggSample
order by ID,Val;

输出結果
 ID  Val  strAgg1  strAgg2      strAgg3      strAgg4
---  ---  -------  -----------  -----------  -------
111  a    c,b,a    a            a,b,c,d,e,f  a,b,c
111  b    c,b      a,b          a,b,c,d,e,f  a,b,c
111  c    c        a,b,c        a,b,c,d,e,f  a,b,c
222  d    f,e,d    a,b,c,d      a,b,c,d,e,f  d,e,f
222  e    f,e      a,b,c,d,e    a,b,c,d,e,f  d,e,f
222  f    f        a,b,c,d,e,f  a,b,c,d,e,f  d,e,f