wmsys.wm_concat( ) 常用的使用方法

SQL> select deptno,ename from emp;
 
DEPTNO ENAME
------ ----------
       YODA
    20 SMITH
    30 ALLEN
    30 WARD
    20 JONES
    30 MARTIN
    30 BLAKE
    10 CLARK
    10 KING
    30 TURNER
    30 JAMES
    20 FORD
    10 MILLER
 
13 rows selected
 
SQL> select deptno,wmsys.wm_concat(ename) from emp group by deptno;
 
DEPTNO WMSYS.WM_CONCAT(ENAME)
------ --------------------------------------------------------------------------------
    10 CLARK,KING,MILLER
    20 SMITH,FORD,JONES
    30 ALLEN,TURNER,JAMES,WARD,BLAKE,MARTIN
       YODA
 

然后再介绍几个其他的用法：

表：

SQL> with test as (
  2      select 10 id,'ab' name,2 id2 from dual
  3      union
  4      select 10,'bc',4 from dual
  5      union
  6      select 10,'ab',3 from dual
  7      union
  8      select 20,'hi',4 from dual
  9      union
 10     select 20,'jk',9 from dual
 11     union
 12     select 20,'mn',6 from dual
 13     )
 14     select id,name,id2 from test
 15  ;
 
        ID NAME        ID2
---------- ---- ----------
        10 ab            2
        10 ab            3
        10 bc            4
        20 hi            4
        20 jk            9
        20 mn            6
 
6 rows selected

使用wmsys.wm_concat后结果

SQL> with test as (
  2      select 10 id,'ab' name,2 id2 from dual
  3      union
  4      select 10,'bc',4 from dual
  5      union
  6      select 10,'ab',3 from dual
  7      union
  8      select 20,'hi',4 from dual
  9      union
 10     select 20,'jk',9 from dual
 11     union
 12     select 20,'mn',6 from dual
 13     )
 14     select id,wmsys.wm_concat( name)  from test group by id
 15  ;
 
        ID WMSYS.WM_CONCAT(NAME)
---------- --------------------------------------------------------------------------------
        10 ab,ab,bc
        20 hi,jk,mn
 

使用wmsys.wm_concat(distinct name):

SQL> with test as (
  2      select 10 id,'ab' name,2 id2 from dual
  3      union
  4      select 10,'bc',4 from dual
  5      union
  6      select 10,'ab',3 from dual
  7      union
  8      select 20,'hi',4 from dual
  9      union
 10     select 20,'jk',9 from dual
 11     union
 12     select 20,'mn',6 from dual
 13     )
 14     select id,wmsys.wm_concat(distinct name)  from test group by id
 15  ;
 
        ID WMSYS.WM_CONCAT(DISTINCTNAME)
---------- --------------------------------------------------------------------------------
        10 ab,bc
        20 hi,jk,mn
 

其他使用：

SQL> with test as (
  2      select 10 id,'ab' name,2 id2 from dual
  3      union
  4      select 10,'bc',4 from dual
  5      union
  6      select 10,'ab',3 from dual
  7      union
  8      select 20,'hi',4 from dual
  9      union
 10     select 20,'jk',9 from dual
 11     union
 12     select 20,'mn',6 from dual
 13     )
 14     select id,wmsys.wm_concat( name) over ()  from test
 15  ;
 
        ID WMSYS.WM_CONCAT(NAME)OVER()
---------- --------------------------------------------------------------------------------
        10 ab,ab,bc,hi,jk,mn
        10 ab,ab,bc,hi,jk,mn
        10 ab,ab,bc,hi,jk,mn
        20 ab,ab,bc,hi,jk,mn
        20 ab,ab,bc,hi,jk,mn
        20 ab,ab,bc,hi,jk,mn
 
6 rows selected
 
SQL> 

----------加了distinct后的效果
SQL> with test as (
  2      select 10 id,'ab' name,2 id2 from dual
  3      union
  4      select 10,'bc',4 from dual
  5      union
  6      select 10,'ab',3 from dual
  7      union
  8      select 20,'hi',4 from dual
  9      union
 10     select 20,'jk',9 from dual
 11     union
 12     select 20,'mn',6 from dual
 13     )
 14     select id,wmsys.wm_concat(distinct  name) over ()  from test
 15  ;
 
        ID WMSYS.WM_CONCAT(DISTINCTNAME)O
---------- --------------------------------------------------------------------------------
        10 ab,bc,hi,jk,mn
        10 ab,bc,hi,jk,mn
        10 ab,bc,hi,jk,mn
        20 ab,bc,hi,jk,mn
        20 ab,bc,hi,jk,mn
        20 ab,bc,hi,jk,mn
 
6 rows selected
 

 

SQL> with test as (
  2      select 10 id,'ab' name,2 id2 from dual
  3      union
  4      select 10,'bc',4 from dual
  5      union
  6      select 10,'ab',3 from dual
  7      union
  8      select 20,'hi',4 from dual
  9      union
 10     select 20,'jk',9 from dual
 11     union
 12     select 20,'mn',6 from dual
 13     )
 14     select id,wmsys.wm_concat(  name) over ( order by id)  from test
 15  ;
 
        ID WMSYS.WM_CONCAT(NAME)OVER(ORDE
---------- --------------------------------------------------------------------------------
        10 ab,ab,bc
        10 ab,ab,bc
        10 ab,ab,bc
        20 ab,ab,bc,hi,jk,mn
        20 ab,ab,bc,hi,jk,mn
        20 ab,ab,bc,hi,jk,mn
 
6 rows selected
 

 

SQL> with test as (
  2      select 10 id,'ab' name,2 id2 from dual
  3      union
  4      select 10,'bc',4 from dual
  5      union
  6      select 10,'ab',3 from dual
  7      union
  8      select 20,'hi',4 from dual
  9      union
 10     select 20,'jk',9 from dual
 11     union
 12     select 20,'mn',6 from dual
 13     )
 14     select id,wmsys.wm_concat(name) over ( order by id,name)  from test
 15  ;
 
        ID WMSYS.WM_CONCAT(NAME)OVER(ORDE
---------- --------------------------------------------------------------------------------
        10 ab,ab
        10 ab,ab
        10 ab,ab,bc
        20 ab,ab,bc,hi
        20 ab,ab,bc,hi,jk
        20 ab,ab,bc,hi,jk,mn
 
6 rows selected

 

SQL> with test as (
  2      select 10 id,'ab' name,2 id2 from dual
  3      union
  4      select 10,'bc',4 from dual
  5      union
  6      select 10,'cd',3 from dual
  7      union
  8      select 20,'hi',4 from dual
  9      union
 10     select 20,'jk',9 from dual
 11     union
 12     select 20,'mn',6 from dual
 13     )
 14     select id,wmsys.wm_concat(  name) over ( partition by id order by id )  from test   --    order by id,name
 15  ;
 
        ID WMSYS.WM_CONCAT(NAME)OVER(PART
---------- --------------------------------------------------------------------------------
        10 ab,bc,cd
        10 ab,bc,cd
        10 ab,bc,cd
        20 hi,jk,mn
        20 hi,jk,mn
        20 hi,jk,mn
 
6 rows selected
 

 

SQL> with test as (
  2      select 10 id,'ab' name,2 id2 from dual
  3      union
  4      select 10,'bc',4 from dual
  5      union
  6      select 10,'cd',3 from dual
  7      union
  8      select 20,'hi',4 from dual
  9      union
 10     select 20,'jk',9 from dual
 11     union
 12     select 20,'mn',6 from dual
 13     )
 14     select id,wmsys.wm_concat(  name) over ( partition by id,name order by id,name )  from test   --    order by id,name
 15  ;
 
        ID WMSYS.WM_CONCAT(NAME)OVER(PART
---------- --------------------------------------------------------------------------------
        10 ab
        10 bc
        10 cd
        20 hi
        20 jk
        20 mn
 
6 rows selected
 

 使用方法：

SQL> SELECT t1.ID, t1.cName,t2.pname--, wmsys.wm_concat(t2.pName)
  2    FROM (with tab1 as (select 1 id, '百度' cname
  3                          from dual
  4                        union
  5                        select 2, 'google'
  6                          from dual
  7                        union
  8                        select 3, '网易' from dual)
  9           select *
 10             from tab1) t1,
 11             (with tab2 as (select 1 id, '研发部' pname
 12                                            from dual
 13                                          union
 14                                          select 1, '市场部'
 15                                            from dual
 16                                            union
 17                                            select 1,'平台架构' from dual
 18                                          union
 19                                          select 2, '研发部'
 20                                            from dual
 21                                          union
 22                                          select 2, '平台架构'
 23                                            from dual
 24                                          union
 25                                          select 3, '市场部' from dual)
 26           select * from tab2) t2
 27            WHERE t1.ID = t2.ID
 28            GROUP BY t1.id, t1.cName,t2.pname;
 
        ID CNAME  PNAME
---------- ------ --------
         2 google 平台架构
         2 google 研发部
         1 百度   研发部
         1 百度   平台架构
         1 百度   市场部
         3 网易   市场部
 
6 rows selected

 

wmsys.wm_concat(t2.pName) 后的结果

SQL> SELECT t1.ID, t1.cName, wmsys.wm_concat(t2.pName)
  2    FROM (with tab1 as (select 1 id, '百度' cname
  3                          from dual
  4                        union
  5                        select 2, 'google'
  6                          from dual
  7                        union
  8                        select 3, '网易' from dual)
  9           select *
 10             from tab1) t1,
 11             (with tab2 as (select 1 id, '研发部' pname
 12                                            from dual
 13                                          union
 14                                          select 1, '市场部'
 15                                            from dual
 16                                            union
 17                                            select 1,'平台架构' from dual
 18                                          union
 19                                          select 2, '研发部'
 20                                            from dual
 21                                          union
 22                                          select 2, '平台架构'
 23                                            from dual
 24                                          union
 25                                          select 3, '市场部' from dual)
 26           select * from tab2) t2
 27            WHERE t1.ID = t2.ID
 28            GROUP BY t1.id, t1.cName;
 
        ID CNAME  WMSYS.WM_CONCAT(T2.PNAME)
---------- ------ --------------------------------------------------------------------------------
        1  百度    平台架构,市场部,研发部
         2  google 平台架构,研发部
         3  网易    市场部