wmsys.wm_concat

最新推荐文章于 2024-11-16 11:41:03 发布

原创最新推荐文章于 2024-11-16 11:41:03 发布 · 217 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

Oracle 专栏收录该内容

3 篇文章

订阅专栏

本文介绍了 Oracle 数据库中的 WM_CONCAT 函数，该函数用于将多行数据聚合为一行，形成逗号分隔的列表。尽管此函数未被官方文档支持，但其用法与 LISTAGG 函数相似，后者为 Oracle 支持的标准函数。

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