3,492
社区成员
发帖
与我相关
我的任务
分享
--修改下排序和逗号问题
with tab as
(
select ',1,1,5,7,3,2,2,1,' id from dual
)
select ',' || wm_concat(id) || ',' id from (
select distinct replace(id,',','') id from
(select substr(id,level,2) id from tab connect by level<=length(id))
where replace(id,',','') is not null
order by to_number(replace(id,',',''))
)
---------------------------------------
ID
-----------
,1,2,3,5,7,
with tab as
(
select ',1,1,5,7,3,2,2,1,' id from dual
)
select wm_concat(distinct replace(id,',','')) from
(select substr(id,level,2) id from tab connect by level<=length(id))
where id > '0' order by replace(id,',','')
--------------------------
wm_concat(distinct replace(id,',',''))
---------
1,2,3,5,7
with tb as(
select 'abc' str, ',1,1,5,7,3,2,2,1,' id from dual
union all
select 'edf' str, ',11,12,15,17,13,12,12,11,' id from dual
)
select str,','||max(id)||','
from (select str,wm_concat(id) over(partition by str order by id) id
from (select distinct str,substr(id,instr(id,',',1,level)+1,instr(id,',',1,level+1)-instr(id,',',1,level)-1) id
from tb
connect by level<length(id)-length(replace(id,',','')) order by str,id))
group by str
---结果
abc ,1,2,3,5,7,
edf ,11,12,13,15,17,
SQL>
SQL> with tb as
2 (
3 select ',1,1,5,7,3,2,2,1,' id from dual
4 )
5 select wm_concat(rs)
6 from (select distinct regexp_substr(id, '[^,]', 1, rownum) rs
7 from tb
8 connect by rownum <= length(regexp_replace(id, '\d', ''))
9 order by rs)
10 ;
WM_CONCAT(RS)
--------------------------------------------------------------------------------
1,2,3,5,7
SQL>
已写入 file afiedt.buf
1 with tb as(
2 select ',1,1,5,7,3,2,2,1,' id from dual
3 )
4 select wm_concat(newid)
5 from(select distinct
6 substr(id,instr(id,',',1,rownum)+1,
7 instr(id,',',1,rownum+1)-instr(id,',',1,rownum)-1) newid
8 from tb
9 connect by rownum <= length(id)-length(replace(id, ',', ''))-1
10* order by newid)
scott@YPCOST> /
WM_CONCAT(NEWID)
---------------------------------------------------------------------
1,2,3,5,7
--没测试,试试:
with tab as(
select ',1,1,5,7,3,2,2,1,' id from dual
)
select ','||wm_concat(newid)over(order by newid)||',' from(
select distinct substr(id,instr(id,',',1,level)+1,
instr(id|,',',1,level+1)-instr(id,',',1,level)-1) newid
from tab
connect by
level <= length(id) - length(replace(id,',',''))-1
)
WITH tb AS(
select 1 flag from dual union all
select 1 from dual union all
select 5 from dual union all
select 7 from dual union all
select 3 from dual union all
select 2 from dual union all
select 2 from dual union all
select 1 from dual
)
select wm_concat(flag)
from (
select distinct flag from tb order by flag)
WM_CONCAT(FLAG)
-------------------
1,2,3,5,7