ORACLE的WMSYS.WM_CONCAT聚合函数

分组聚合函数WMSYS.WM_CONCAT

合并某一字段：

SQL> select WMSYS.WM_CONCAT(ename) from emp;
WMSYS.WM_CONCAT(ENAME)

SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MIL

根据deptno,合并ename：

SQL> select deptno,WMSYS.WM_CONCAT(ename) from emp group by deptno;
DEPTNO WMSYS.WM_CONCAT(ENAME)

    10 CLARK,MILLER,KING
    20 SMITH,FORD,ADAMS,SCOTT,JONES
    30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD

WMSYS.WM_CONCAT(目标字段)over(partition BY 分组 order by 排序)

SQL> select deptno,WMSYS.WM_CONCAT(ename) over (partition by deptno) from emp;
DEPTNO WMSYS.WM_CONCAT(ENAME)OVER(PAR

    10 CLARK,KING,MILLER
    10 CLARK,KING,MILLER
    10 CLARK,KING,MILLER
    20 JONES,FORD,ADAMS,SMITH,SCOTT
    20 JONES,FORD,ADAMS,SMITH,SCOTT
    20 JONES,FORD,ADAMS,SMITH,SCOTT
    20 JONES,FORD,ADAMS,SMITH,SCOTT
    20 JONES,FORD,ADAMS,SMITH,SCOTT
    30 WARD,TURNER,ALLEN,JAMES,BLAKE,MARTIN
    30 WARD,TURNER,ALLEN,JAMES,BLAKE,MARTIN
    30 WARD,TURNER,ALLEN,JAMES,BLAKE,MARTIN
    30 WARD,TURNER,ALLEN,JAMES,BLAKE,MARTIN
    30 WARD,TURNER,ALLEN,JAMES,BLAKE,MARTIN
    30 WARD,TURNER,ALLEN,JAMES,BLAKE,MARTIN
14 rows selected

SQL> select distinct deptno,WMSYS.WM_CONCAT(ename) over (partition by deptno) from emp;
DEPTNO WMSYS.WM_CONCAT(ENAME)OVER(PAR

    10 CLARK,KING,MILLER
    30 WARD,TURNER,ALLEN,JAMES,BLAKE,MARTIN
    20 JONES,FORD,ADAMS,SMITH,SCOTT