作用
可以实现将多列记录聚合为一列记录，实现数据的压缩
语法结构
listagg(measure_expr，delimiter) within group ( order by order_by_clause);
解释：measure_expr可以是基于任何列的表达式
delimiter分隔符，默认为NULL
order_by_clause决定了列值的拼接顺序
举例
普通函数，对工资进行排序，并按照逗号进行拼接
SQL> select listagg(ename,‘,‘)within group(order by sal)name from emp; NAME ---------------------------------------------------------------------------------------------------- SMITH,JAMES,ADAMS,MARTIN,WARD,MILLER,TURNER,ALLEN,CLARK,BLAKE,JONES,FORD,SCOTT,KING
分组函数
SQL> select deptno,listagg(ename,‘,‘)within group(order by sal)name from emp group by deptno;
DEPTNO NAME
---------- ----------------------------------------------------------------------------------------------------
10 MILLER,CLARK,KING
20 SMITH,ADAMS,JONES,FORD,SCOTT
30 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
分析函数
SQL> select deptno,ename,sal,listagg(ename,‘,‘)within group(order by sal)over(partition by deptno)name from emp;
DEPTNO ENAME SAL NAME
---------- ---------- ---------- ----------------------------------------
10 MILLER 1300 MILLER,CLARK,KING
10 CLARK 2450 MILLER,CLARK,KING
10 KING 5000 MILLER,CLARK,KING
20 SMITH 800 SMITH,ADAMS,JONES,SCOTT,FORD
20 ADAMS 1100 SMITH,ADAMS,JONES,SCOTT,FORD
20 JONES 2975 SMITH,ADAMS,JONES,SCOTT,FORD
20 SCOTT 3000 SMITH,ADAMS,JONES,SCOTT,FORD
20 FORD 3000 SMITH,ADAMS,JONES,SCOTT,FORD
30 JAMES 950 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
30 MARTIN 1250 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
30 WARD 1250 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
30 TURNER 1500 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
30 ALLEN 1600 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
30 BLAKE 2850 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
14 rows selected.