加载中…Oracle 连接字符串的方法
(2012-11-26 22:14:16)
标签：
oracle连接字符串listaggwm_concatsys_connect_by_pathit |
分类： Oracle |
方法一：wmsys.wm_concat(column)
Select u_id, wmsys.wm_concat(goods ||
'(' || num || '斤)' ) goods_sum
方法二：listagg
方法三：sys_connect_by_path(column,<分隔符>)
下面是上面几种方法的实例（在Oracle 11g版本中运行正确）：
实例：
方法一：用listagg(,',') within group（）
SQL code
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
WITH temp1 AS( select 'a' as username,1 as deptid from dual union all select 'b',1 from dual union all select 'c',1 from dual union all select 'd',2 from dual union all select 'e',2 from dual ), temp2 AS( select 1 as deptid,'部门1' as deptname from dual union all select 2 ,'部门2' from dual ) select p.deptid, listagg(t.username,',') within group (order by t.username) as username, p.deptname from temp1 t,temp2 p where t.deptid=p.deptid group by p.deptid,p.deptname order by p.deptid ------------------------- deptid userName deptName 1 a,b,c 部门1 2 d,e 部门2 |
方法二：用wm_concat()
SQL code
|
1 2 3 4 5 6 7 8 9 10 |
select p.deptid, wm_concat(t.username) as username, p.deptname from temp1 t,temp2 p where t.deptid=p.deptid group by p.deptid,p.deptname order by p.deptid |
方法三：用CONNECT BY
SQL code
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
select deptid, ltrim(max(sys_connect_by_path(username,',')),','), deptname from ( select p.deptid,t.username,p.deptname, row_number()over(partition by t.deptid order by t.username) as ar from temp1 t,temp2 p where t.deptid=p.deptid ) start with ar=1 connect by prior ar=ar-1 and username=prior username group by deptid,deptname order by deptid |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------如有错误，请指教！
--------------技术交流QQ：1732035211
-------------技术交流邮箱：1732035211@qq.com
喜欢
0
赠金笔