Oracle函数wmsys.wm_concat的使用
Oracle函数wmsys.wm_concat的使用
首先介绍语法:
wmsys.wm_concat
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 unsupported by Oracle, meaning it should not be used in production systems. The LISTAGG function, which can produce the same output asWM_CONCAT is both documented and supported by Oracle.
例子:
- select t1.main_id,
- to_char(wmsys.wm_concat(t1.send_id || '||' || t2.realname || '||' ||
- 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
更多Oracle相关信息见Oracle 专题页面 http://www.bkjia.com/topicnews.aspx?tid=12
评论暂时关闭