Oracle函数wmsys.wm_concat的使用

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

psyuhen

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

阅读量3k

点赞数 1

分类专栏： Oracle学习文章标签： oracle user output path list sql

本文链接：https://blog.csdn.net/psyuhen/article/details/7492460

版权

Oracle学习专栏收录该内容

12 篇文章 0 订阅

订阅专栏

首先介绍语法：

wmsys.wm_concat

Definition:
The Oracle PL/SQL WM_CONCAT function is used to aggregate data from a number of rows into a single row, giving a list of data associated with a specific value. In effect, it cross-tabulates a comma delimited list.

Note that WM_CONCAT is undocumented and unsupported by Oracle, meaning it should not be used in production systems. The LISTAGG function, which can produce the same output asWM_CONCAT is both documented and supported by Oracle.

例子：

select t1.main_id,
       to_char(wmsys.wm_concat(t1.send_id || '||' || t2.realname || '||' ||
                               t2.IMG_PATH)) sendidlist
  from LCMS_SEND_USER t1, LCMS_USER_STUD t2
 group by t1.main_id;

结果：

网络中也有这样使用：

下面只是收藏：

SQL> select id,wmsys.wm_concat(name) over (order by id) name from idtable;

        ID NAME
---------- --------------------------------------------------------------------------------
        10 ab,bc,cd
        10 ab,bc,cd
        10 ab,bc,cd
        20 ab,bc,cd,hi,ij,mn
        20 ab,bc,cd,hi,ij,mn
        20 ab,bc,cd,hi,ij,mn

6 rows selected

SQL> select id,wmsys.wm_concat(name) over (order by id,name) name from idtable;

        ID NAME
---------- --------------------------------------------------------------------------------
        10 ab
        10 ab,bc
        10 ab,bc,cd
        20 ab,bc,cd,hi
        20 ab,bc,cd,hi,ij
        20 ab,bc,cd,hi,ij,mn

6 rows selected

个人觉得这个用法比较有趣.

SQL> select id,wmsys.wm_concat(name) over (partition by id) name from idtable;

        ID NAME
---------- --------------------------------------------------------------------------------
        10 ab,bc,cd
        10 ab,bc,cd
        10 ab,bc,cd
        20 hi,ij,mn
        20 hi,ij,mn
        20 hi,ij,mn

6 rows selected

SQL> select id,wmsys.wm_concat(name) over (partition by id,name) name from idtable;

        ID NAME
---------- --------------------------------------------------------------------------------
        10 ab
        10 bc
        10 cd
        20 hi
        20 ij
        20 mn

6 rows selected