WITH A AS(SELECT 1 id,'60,65' item FROM dual UNION ALL SELECT 1,'87,35' FROM dual union all select 2,'40,68,70' from dual)
select a.id,substr(a.item,instr(','||a.item,',',1,b.rn),instr(a.item||',',',',1,b.rn)-instr(','||a.item,',',1,b.rn))item from a, (select * from (SELECT DISTINCT id FROM A), (SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM<=( select max(length(item)-length(replace(item,',')))+1 from a)))b WHERE A.ID=b.ID and length(a.item)-length(replace(a.item,',',''))+1>=b.rn
另一种思路:with tmp as (select 1 id, '60,65' item from dual union all select 1 id, '87,35' item from dual union all select 2 id, '40,68,70' item from dual ) select distinct id, substr(regexp_substr(',' || item, ',([^,]+)', 1, level), 2) as "item"--, level,rownum from tmp connect by level <= length(regexp_replace(item, '[^,]', '')) + 1 order by id
UNION ALL SELECT 1,'87,35' FROM dual
union all select 2,'40,68,70' from dual)
select a.id,substr(a.item,instr(','||a.item,',',1,b.rn),instr(a.item||',',',',1,b.rn)-instr(','||a.item,',',1,b.rn))item
from a,
(select * from
(SELECT DISTINCT id FROM A),
(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM<=(
select max(length(item)-length(replace(item,',')))+1 from a)))b
WHERE A.ID=b.ID
and length(a.item)-length(replace(a.item,',',''))+1>=b.rn
(select 1 id, '60,65' item from dual
union all
select 1 id, '87,35' item from dual
union all
select 2 id, '40,68,70' item from dual
)
select distinct id,
substr(regexp_substr(',' || item, ',([^,]+)', 1, level), 2) as "item"--, level,rownum
from tmp
connect by level <= length(regexp_replace(item, '[^,]', '')) + 1
order by id