create table t_test(f1 varchar2(100),f2 varchar2(100)); insert into t_test values('a','1,2,3,4,5'); insert into t_test values('b','2,3,4,5');select f1,f2 from (with test as (select f1,f2 from t_test) select f1,substr(t.ca,instr(t.ca, ',', 1, c.lv) + 1,instr(t.ca, ',', 1, c.lv + 1) - (instr(t.ca, ',', 1, c.lv) + 1)) AS f2 from (select f1,',' || f2 || ',' AS ca,length(f2 || ',') - nvl(length(REPLACE(f2, ',')), 0) AS cnt FROM test) t, (select LEVEL lv from dual CONNECT BY LEVEL <= 100) c where c.lv <= t.cnt ) order by f1,f2
WITH tmp AS ( SELECT 'a' col1, '1,2,3' col2 from dual UNION ALL SELECT 'b', '2,4' from dual ) SELECT * FROM (SELECT col1, replace(substr(col2||',',INSTR('1,2,3',',',LEVEL-1),INSTR('1,2,3',',',LEVEL)-1),',') NAME from tmp CONNECT BY LEVEL <= LENGTH(col2)+2-LENGTH(REPLACE(col2,',')) ) x WHERE x.name is not NULL group BY x.col1, x.name ORDER BY col1 ;
select col1 , substr( ','||col2||',' , instr(','||col2||',' , ',' , 1 , X ) + 1 , instr(','||col2||',' , ',' , 1 , X+1 ) - instr(','||col2||',' , ',' , 1 , X ) - 1 ) from ( select 'a','1,2,3,4' from dual union select 'b', '1,2,3' from dual ) T , ( select rownum X from all_objects where rownum < 20 ) where 1=1 and x <= length(col2)-lenght(replace(col2,',','')) + 1
有点错误, select col1 , substr( ','||col2||',' , instr(','||col2||',' , ',' , 1 , X ) + 1 , instr(','||col2||',' , ',' , 1 , X+1 ) - instr(','||col2||',' , ',' , 1 , X ) - 1 ) from ( select 'a' col1 ,'1,2,3,4' col2 from dual union select 'b', '1,2,3' from dual ) T , ( select rownum X from all_objects where rownum < 20 ) -- 上面这句可以用任意的表,只要有足够的记录数就好了。 where 1=1 and x <= length(col2)-lenght(replace(col2,',','')) + 1
insert into t_test values('a','1,2,3,4,5');
insert into t_test values('b','2,3,4,5');select f1,f2 from
(with test as (select f1,f2 from t_test)
select f1,substr(t.ca,instr(t.ca, ',', 1, c.lv) + 1,instr(t.ca, ',', 1, c.lv + 1) - (instr(t.ca, ',', 1, c.lv) + 1)) AS f2
from (select f1,',' || f2 || ',' AS ca,length(f2 || ',') - nvl(length(REPLACE(f2, ',')), 0) AS cnt FROM test) t,
(select LEVEL lv from dual CONNECT BY LEVEL <= 100) c where c.lv <= t.cnt )
order by f1,f2
SELECT 'a' col1, '1,2,3' col2 from dual UNION ALL
SELECT 'b', '2,4' from dual
) SELECT * FROM (SELECT col1, replace(substr(col2||',',INSTR('1,2,3',',',LEVEL-1),INSTR('1,2,3',',',LEVEL)-1),',') NAME
from tmp
CONNECT BY LEVEL <= LENGTH(col2)+2-LENGTH(REPLACE(col2,',')) ) x
WHERE x.name is not NULL
group BY x.col1, x.name
ORDER BY col1
;
, substr( ','||col2||','
, instr(','||col2||',' , ',' , 1 , X ) + 1
, instr(','||col2||',' , ',' , 1 , X+1 ) - instr(','||col2||',' , ',' , 1 , X ) - 1
)
from ( select 'a','1,2,3,4' from dual union select 'b', '1,2,3' from dual ) T
, ( select rownum X from all_objects where rownum < 20 )
where 1=1
and x <= length(col2)-lenght(replace(col2,',','')) + 1
, substr( ','||col2||','
, instr(','||col2||',' , ',' , 1 , X ) + 1
, instr(','||col2||',' , ',' , 1 , X+1 ) - instr(','||col2||',' , ',' , 1 , X ) - 1
)
from ( select 'a' col1 ,'1,2,3,4' col2 from dual union select 'b', '1,2,3' from dual ) T
, ( select rownum X from all_objects where rownum < 20 )
-- 上面这句可以用任意的表,只要有足够的记录数就好了。
where 1=1
and x <= length(col2)-lenght(replace(col2,',','')) + 1