ORACLE的WMSYS.WM_CONCAT聚合函数

最新推荐文章于 2021-04-14 08:51:47 发布

TUBER727

最新推荐文章于 2021-04-14 08:51:47 发布

阅读量1.2k

点赞数

分类专栏： Oracle

Oracle 专栏收录该内容

51 篇文章 0 订阅

订阅专栏

分组聚合函数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