17,377
社区成员
发帖
与我相关
我的任务
分享
--------9i
SQL> with tab as
2 (
3 select 1 id ,'a' code, '张三' name from dual union all
4 select 2 id ,'a' code, '李四' name from dual union all
5 select 3 id ,'c' code, '王五' name from dual
6 )
7 SELECT MAX(substr(sys_connect_by_path(t.name, '、'), 2)) str
8 FROM (SELECT code,
9 name,
10 row_number() over(PARTITION BY code ORDER BY name) rn
11 FROM tab
12 where code = 'a') t
13 START WITH rn = 1
14 CONNECT BY rn = PRIOR rn + 1
15 AND code = PRIOR code
16 ;
STR
--------------------------------------------------------------------------------
张三、李四
----10g
SQL>
SQL> with tab as
2 (
3 select 1 id ,'a' code, '张三' name from dual union all
4 select 2 id ,'a' code, '李四' name from dual union all
5 select 3 id ,'c' code, '王五' name from dual
6 )
7 select replace(wm_concat(name),',','、') from tab where code = 'a'
8 ;
REPLACE(WM_CONCAT(NAME),',','?
--------------------------------------------------------------------------------
张三、李四
SQL>
--成哥写漏了
select replace(wm_concat(name),',','、')
from mytab
where code='a'
select replace(wm_concat(name),'、')
from mytab
where code='a'