wmsys.wm_concat

最新推荐文章于 2024-11-11 08:41:40 发布

intrejj

最新推荐文章于 2024-11-11 08:41:40 发布

阅读量188

点赞数

分类专栏： Oracle 文章标签：数据库

本文链接：https://blog.csdn.net/intrejj/article/details/84263630

版权

Oracle 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

wmsys.wm_conc at

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 unsu pported by Oracle, meaning it should not be used in produ ction systems. The LISTAGG function, which can produce the same output as WM_CONCAT is both documented and supported by Oracle.

例子：

select 

t1.main_id

,
to_char(wmsys.wm_concat
(t1.send_id || '||' || t2.realn

ame || '||' ||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