oracle 的wmsys.wm_concat函数用法

无意中碰到wmsys.wm_concat这个函数很好很强大. 怕有天忘了先记下来自己懒得写例子,网上抄来一片 谢谢你了 哥们. 原文网址:http://space.itpub.net/?13387766/viewspace-448841 今天才发现了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