最近在学习的过程中,发现一个挺有意思的函数,它可实现对列值的拼接。下面我们来看看其具体用法。
用法:
下面对该函数进行举例说明:
普通函数,对工资进行排序,用逗号进行拼接。
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.