Oracle ListAgg 和 wm_concat函数

来源：互联网发布：法语助手软件过期编辑：程序博客网时间：2024/05/21 13:59

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

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

create table strAggSample(ID,Val) asselect 111,'a' from dual union allselect 111,'b' from dual union allselect 111,'c' from dual union allselect 222,'d' from dual union allselect 222,'e' from dual union allselect 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 strAggSampleorder by ID,Val;

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

0 0