[转]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