listagg小记录

来源：互联网发布：关键词优化在哪里编辑：程序博客网时间：2024/05/18 19:47

listagg的作用是将分组范围内的所有行特定列的记录加以合并成行。函数签名中的measure_expr为分组中每个列的表达式，而delimiter为合并分割符。如果delimiter不设置的话，就表示无分割符。

中间within group后面的order_by_clause表示的是进行合并中要遵守的排序顺序。而后面的over子句表明listagg是具有分析函数analyze funcation特性的。具体采用listagg有三个场景。

1、对于无分组

SQL> select * from emp where deptno=30;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30

7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30

7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30

7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30

7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30

7900 JAMES CLERK 7698 1981-12-3 950.00 30

6 rows selected

--按照empno进行排序

SQL> select listagg(ename,' , ') within group (order byempno) from emp where deptno=30;

LISTAGG(ENAME,',')WITHINGROUP(

------------------------------------------------------------

ALLEN , WARD , MARTIN , BLAKE , TURNER , JAMES

2、在有分组条件下的listagg使用

SQL> select deptno, listagg(ename,' ,') within group (order by empno) from emp group by deptno;

DEPTNO LISTAGG(ENAME,',')WITHINGROUP(

------ -------------------------------------

10 CLARK ,KING ,MILLER

20 SMITH ,JONES ,SCOTT ,ADAMS ,FORD

30 ALLEN ,WARD ,MARTIN ,BLAKE ,TURNER ,JAMES

3、使用over分组情况

SQL> select deptno, ename, listagg(ename, ' , ') within group (order by empno) over (partition by deptno) as emp_list from emp

2 where hiredate<=add_months(sysdate,-10*12);

DEPTNO ENAME EMP_LIST

------ ------

10 CLARK CLARK , KING , MILLER

10 KING CLARK , KING , MILLER

10 MILLER CLARK , KING , MILLER

20 SMITH SMITH , JONES , SCOTT , ADAMS , FORD

20 JONES SMITH , JONES , SCOTT , ADAMS , FORD

20 SCOTT SMITH , JONES , SCOTT , ADAMS , FORD

20 ADAMS SMITH , JONES , SCOTT , ADAMS , FORD

20 FORD SMITH , JONES , SCOTT , ADAMS , FORD

30 ALLEN ALLEN , WARD , MARTIN , BLAKE , TURNER , JAMES

30 WARD ALLEN , WARD , MARTIN , BLAKE , TURNER , JAMES

30 MARTIN ALLEN , WARD , MARTIN , BLAKE , TURNER , JAMES

30 BLAKE ALLEN , WARD , MARTIN , BLAKE , TURNER , JAMES

30 TURNER ALLEN , WARD , MARTIN , BLAKE , TURNER , JAMES

30 JAMES ALLEN , WARD , MARTIN , BLAKE , TURNER , JAMES

14 rows selected

0 0