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
--没测试,试试: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 )
已写入 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
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,7SQL>
with tab as (select '1,1,3,7,3,2,2,1,' as chr from dual) select replace(','||t.num0||','||t.num1||','||t.num2||','||t.num3||','||t.num4||','||t.num5||','||t.num6||','||t.num7||','||t.num8||','||t.num9,',-1','')as result from (select case when instr((select chr from tab),'0',1,1)>0 then '0' else '-1' end as num0, case when instr((select chr from tab),'1',1,1)>0 then '1' else '-1' end as num1, case when instr((select chr from tab),'2',1,1)>0 then '2' else '-1' end as num2, case when instr((select chr from tab),'3',1,1)>0 then '3' else '-1' end as num3, case when instr((select chr from tab),'4',1,1)>0 then '4' else '-1' end as num4, case when instr((select chr from tab),'5',1,1)>0 then '5' else '-1' end as num5, case when instr((select chr from tab),'6',1,1)>0 then '6' else '-1' end as num6, case when instr((select chr from tab),'7',1,1)>0 then '7' else '-1' end as num7, case when instr((select chr from tab),'8',1,1)>0 then '8' else '-1' end as num8, case when instr((select chr from tab),'9',1,1)>0 then '9' else '-1' end as num9 from dual )t
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 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 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,
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
--没测试,试试: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
)
已写入 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
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,7SQL>
必须把 rownum 改成 其它名词,如 level,请试一下。
select replace(','||t.num0||','||t.num1||','||t.num2||','||t.num3||','||t.num4||','||t.num5||','||t.num6||','||t.num7||','||t.num8||','||t.num9,',-1','')as result from (select
case when instr((select chr from tab),'0',1,1)>0 then '0' else '-1' end as num0,
case when instr((select chr from tab),'1',1,1)>0 then '1' else '-1' end as num1,
case when instr((select chr from tab),'2',1,1)>0 then '2' else '-1' end as num2,
case when instr((select chr from tab),'3',1,1)>0 then '3' else '-1' end as num3,
case when instr((select chr from tab),'4',1,1)>0 then '4' else '-1' end as num4,
case when instr((select chr from tab),'5',1,1)>0 then '5' else '-1' end as num5,
case when instr((select chr from tab),'6',1,1)>0 then '6' else '-1' end as num6,
case when instr((select chr from tab),'7',1,1)>0 then '7' else '-1' end as num7,
case when instr((select chr from tab),'8',1,1)>0 then '8' else '-1' end as num8,
case when instr((select chr from tab),'9',1,1)>0 then '9' else '-1' end as num9 from dual )t
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 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 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,