--假设现在有这么个需求:
AID ADDRESS
1 1 北环路
2 1 南阳路
3 2 商城路
4 2 王府井
得到这样的结果:
AID WM_CONCAT(ADDRESS)
1 1 北环路,南阳路
2 2 商城路,王府井
1,创建临时表with...as
with C as
(
select 1 id ,'北环路' address from dual
union all
select 1 id, '南阳路' address from dual
union all
select 2 id ,'商城路' address from dual
union all
select 2 id, '王府井' address from dual
)
select * from C
--------------------得到结果-----------------------------------------------------------------------------------------------------
ID ADDRESS
1 1 北环路
2 1 南阳路
3 2 商城路
4 2 王府井
2,相同id合并相关列wm_concat
with C as
(
select 1 id ,'北环路' address from dual
union all
select 1 id, '南阳路' address from dual
union all
select 2 id ,'商城路' address from dual
union all
select 2 id, '王府井' address from dual
)
select id,wm_concat(address) from C group by id
--------------------得到结果-----------------------------------------------------------------------------------------------------
ID WM_CONCAT(ADDRESS)
1 1 北环路,南阳路
2 2 商城路,王府井
-------------------------------------------华丽的分隔符--------------------------------------------------------
现在如果想将,号换成其它,比如“-”:
select id,regexp_replace(wm_concat(address),",","-") from C group by id
特别说明:wm_concat的用法:
================================================================
wm_concat(column)函数 http://database.51cto.com/art/201010/231126.htm
================================================================
相关推荐:
Mysql中类似此函数:group_concat
http://blog.csdn.net/xb12369/article/details/42002687