oracle wm_concat函数用法

wmsys.wm_concat函数，它的作用是以','链接字符

例子如下：

SQL> create table idtable (id number,name varchar2(30));

Table created

SQL> insert into idtable values(10,'ab');

1 row inserted

SQL> insert into idtable values(10,'bc');

1 row inserted

SQL> insert into idtable values(10,'cd');

1 row inserted

SQL> insert into idtable values(20,'hi');

1 row inserted

SQL> insert into idtable values(20,'ij');

1 row inserted
SQL> insert into idtable values(20,'mn');

1 row inserted

SQL> select * from idtable;

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

6 rows selected
SQL> select id,wmsys.wm_concat(name) name from idtable
2 group by id;

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

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

原文地址:http://bbs.delit.cn/thread-206-1-1.html

转载请注明出处:

撰写人:度量科技http://www.delit.cn