oracle 内部函数 wmsys.wm_concat

最新推荐文章于 2021-01-30 14:56:00 发布

原创最新推荐文章于 2021-01-30 14:56:00 发布 · 2.8k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #行转列 #wm_concat

oracle 函数杂谈专栏收录该内容

7 篇文章

订阅专栏

本文详细介绍了Oracle内部函数WM_CONCAT的功能及使用方法，通过多个实例展示了如何利用此函数进行行转列操作，同时对比了不同版本间的差异，并推荐了进一步学习资源。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

今天和大家说说wmsys.wm_concat这个oracle内部函数，为什么说这个是内部函数呢，因为oracle对外并没有对外公布(在oracle官方文档上面没有发现)，这个一旦在生产环境出了问题,oracle是不管的，好像每个数据库版本这个函数还有点不一样，大家用的时候小心点好了，在测试库和生产库版本不一致的时候。

那就先说说的版本吧，我今天演示的环境是10.2.0.1.0 - 64bit，我的测试数据是SCOTT.EMP 这张表。数据如下

其实这个函数也没简单，我主要运行几个例子，大家看看效果

SQL> SELECT T.DEPTNO,wmsys.wm_concat(T.ENAME)  CONCAT FROM EMP T
  2  GROUP BY T.DEPTNO;

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

SQL> 
SQL> 
SQL> SELECT T.DEPTNO,wmsys.wm_concat(T.ENAME) over ( order by T.DEPTNO)  CONCAT FROM EMP T;

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

    DEPTNO CONCAT
---------- ----------------------------------------------------------------------------------------------------
        30 CLARK,KING,MILLER,JONES,FORD,ADAMS,SMITH,SCOTT,WARD,TURNER,ALLEN,JAMES,BLAKE,MARTIN
        30 CLARK,KING,MILLER,JONES,FORD,ADAMS,SMITH,SCOTT,WARD,TURNER,ALLEN,JAMES,BLAKE,MARTIN
        30 CLARK,KING,MILLER,JONES,FORD,ADAMS,SMITH,SCOTT,WARD,TURNER,ALLEN,JAMES,BLAKE,MARTIN

14 rows selected.

SQL> 
SQL> 
SQL> SELECT T.DEPTNO,wmsys.wm_concat(T.ENAME) over ( order by T.DEPTNO,T.ENAME)  CONCAT FROM EMP T;

    DEPTNO CONCAT
---------- ----------------------------------------------------------------------------------------------------
        10 CLARK
        10 CLARK,KING
        10 CLARK,KING,MILLER
        20 CLARK,KING,MILLER,ADAMS
        20 CLARK,KING,MILLER,ADAMS,FORD
        20 CLARK,KING,MILLER,ADAMS,FORD,JONES
        20 CLARK,KING,MILLER,ADAMS,FORD,JONES,SCOTT
        20 CLARK,KING,MILLER,ADAMS,FORD,JONES,SCOTT,SMITH
        30 CLARK,KING,MILLER,ADAMS,FORD,JONES,SCOTT,SMITH,ALLEN
        30 CLARK,KING,MILLER,ADAMS,FORD,JONES,SCOTT,SMITH,ALLEN,BLAKE
        30 CLARK,KING,MILLER,ADAMS,FORD,JONES,SCOTT,SMITH,ALLEN,BLAKE,JAMES

    DEPTNO CONCAT
---------- ----------------------------------------------------------------------------------------------------
        30 CLARK,KING,MILLER,ADAMS,FORD,JONES,SCOTT,SMITH,ALLEN,BLAKE,JAMES,MARTIN
        30 CLARK,KING,MILLER,ADAMS,FORD,JONES,SCOTT,SMITH,ALLEN,BLAKE,JAMES,MARTIN,TURNER
        30 CLARK,KING,MILLER,ADAMS,FORD,JONES,SCOTT,SMITH,ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

14 rows selected.

SQL> 
SQL> 
SQL> 
SQL> SELECT T.DEPTNO,wmsys.wm_concat(T.ENAME) over (partition by T.DEPTNO order by T.DEPTNO,T.ENAME)  CONCAT FROM EMP T;

    DEPTNO CONCAT
---------- ----------------------------------------------------------------------------------------------------
        10 CLARK
        10 CLARK,KING
        10 CLARK,KING,MILLER
        20 ADAMS
        20 ADAMS,FORD
        20 ADAMS,FORD,JONES
        20 ADAMS,FORD,JONES,SCOTT
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN
        30 ALLEN,BLAKE
        30 ALLEN,BLAKE,JAMES

    DEPTNO CONCAT
---------- ----------------------------------------------------------------------------------------------------
        30 ALLEN,BLAKE,JAMES,MARTIN
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

14 rows selected.

这几个例子大家常用的效果应该是用在行转列上面，其他的例子大家就看着玩好了。

在11G里面oracle正式推出了LISTAGG 这个函数，有兴趣的的可以去查看。

推荐一个oracle的帖子，有这个介绍

http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php