[转]Oracle 分组聚合二种写法，listagg和wmsys.wm_concat

本文转自：http://www.cnblogs.com/ycdx2001/p/3502495.html

with temp as(
select 'China' nation ,'Guangzhou' city from dual union all select 'China' nation ,'Shanghai' city from dual union all select 'China' nation ,'Beijing' city from dual union all select 'USA' nation ,'New York' city from dual union all select 'USA' nation ,'Bostom' city from dual union all select 'Japan' nation ,'Tokyo' city from dual ) select nation,listagg(city,',') within GROUP (order by city) from temp group by nation

select goodsid,listagg(ss.StorageNo,',')  within group (order by ss.StorageNo) StorageNo from StorageGoods ssg left join storage ss on ssg.storageid=ss.guid group by goodsid

select goodsid,wmsys.wm_concat(ss.StorageNo) StorageNo
      from StorageGoods ssg
      left join storage ss on ssg.storageid=ss.guid --where ssg.goodsid=sd.goodsid group by goodsid

 

 

oracle合并列的函数wm_concat的使用详解

http://www.jb51.net/article/37604.htm

 

oracle wm_concat(column)函数使我们经常会使用到的，下面就教您如何使用oracle wm_concat(column)函数实现字段合并，如果您对oracle wm_concat(column)函数使用方面感兴趣的话，不妨一看。
shopping:
-----------------------------------------
u_id       goods            num
------------------------------------------
1                苹果                2
2                 梨子               5
1                 西瓜               4
3                 葡萄               1
3                香蕉                1
1               橘子                 3
=======================
想要的结果为:
--------------------------------
u_id          goods_sum
____________________
1              苹果,西瓜,橘子
2              梨子
3              葡萄,香蕉
---------------------------------
1.select u_id, wmsys.wm_concat(goods) goods_sum  2. 3.from shopping  4. 5.group by u_id 
想要的结果2:
--------------------------------
u_id          goods_sum
____________________
1              苹果(2斤),西瓜(4斤),橘子(3斤)
2              梨子(5斤)
3              葡萄(1斤),香蕉(1斤)
---------------------------------
使用oracle wm_concat(column)函数实现：
select u_id, wmsys.wm_concat(goods || '(' || num || '斤)' ) goods_sum 
from shopping  
group by u_id 
mysql---group_concat