Oracle函数listagg使用

作用

可以实现将多列记录聚合为一列记录，实现数据的压缩

语法结构

listagg(measure_expr，delimiter) within group ( order by order_by_clause);

解释：measure_expr可以是基于任何列的表达式

　　delimiter分隔符，默认为NULL

　　order_by_clause决定了列值的拼接顺序

举例

普通函数，对工资进行排序，并按照逗号进行拼接

1 SQL> select listagg(ename,',')within group(order by sal)name from emp;
2 
3 NAME
4 ----------------------------------------------------------------------------------------------------
5 SMITH,JAMES,ADAMS,MARTIN,WARD,MILLER,TURNER,ALLEN,CLARK,BLAKE,JONES,FORD,SCOTT,KING

分组函数

1 SQL> select deptno,listagg(ename,',')within group(order by sal)name from emp group by deptno;
2 
3     DEPTNO NAME
4 ---------- ----------------------------------------------------------------------------------------------------
5     10 MILLER,CLARK,KING
6     20 SMITH,ADAMS,JONES,FORD,SCOTT
7     30 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE

分析函数

 1 SQL> select deptno,ename,sal,listagg(ename,',')within group(order by sal)over(partition by deptno)name from emp;
 2 
 3     DEPTNO ENAME             SAL NAME
 4 ---------- ---------- ---------- ----------------------------------------
 5         10 MILLER           1300 MILLER,CLARK,KING
 6         10 CLARK            2450 MILLER,CLARK,KING
 7         10 KING             5000 MILLER,CLARK,KING
 8         20 SMITH             800 SMITH,ADAMS,JONES,SCOTT,FORD
 9         20 ADAMS            1100 SMITH,ADAMS,JONES,SCOTT,FORD
10         20 JONES            2975 SMITH,ADAMS,JONES,SCOTT,FORD
11         20 SCOTT            3000 SMITH,ADAMS,JONES,SCOTT,FORD
12         20 FORD             3000 SMITH,ADAMS,JONES,SCOTT,FORD
13         30 JAMES             950 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
14         30 MARTIN           1250 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
15         30 WARD             1250 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
16         30 TURNER           1500 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
17         30 ALLEN            1600 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
18         30 BLAKE            2850 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
19 
20 14 rows selected.