ORACLE的WMSYS.WM_CONCAT聚合函数

最新推荐文章于 2024-01-04 07:42:21 发布

转载最新推荐文章于 2024-01-04 07:42:21 发布 · 1.2k 阅读

Oracle 专栏收录该内容

51 篇文章

订阅专栏

本文介绍了 Oracle 数据库中 WMSYS.WM_CONCAT 函数的基本用法及应用场景，包括如何使用该函数合并同一字段的不同值以及如何根据特定分组进行合并操作。此外还展示了如何结合 OVER 子句实现更灵活的数据汇总。

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