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