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  网易    市场部