把下面的表名test_a换成你自己的表名即可: select name,regexp_substr(id,'[^,]+',1,n) from test_a left join (select level,rownum n from dual connect by level < 10) on 1=1 where regexp_substr(id,'[^,]+',1,n) is not null
1、原始需求是啥,肯定不是为了写sql而写sql吧。很多的时候可以曲线救国 2、如果真想用一个sql完成,那么 SQL> select * from a; ID USERID ---------- -------------------- 1 aa,22,bb,cc 2 dd2,3aSQL> SQL> select id, userid 2 from (select id, 3 substr(',' || userid || ',', 4 instr(',' || userid || ',', ',', 1, rn) + 1, 5 instr(',' || userid || ',', ',', 1, rn + 1) - 6 instr(',' || userid || ',', ',', 1, rn) - 1) as userid 7 from A, 8 (select rownum rn 9 from all_objects 10 where rownum <= (select max(length(userid) - 11 length(replace(userid, ',')) + 1) 12 from A))) 13 where userid is not null; ID USERID ---------- -------------------- 1 aa 1 22 1 bb 1 cc 2 dd2 2 3a6 rows selected.
select name,regexp_substr(id,'[^,]+',1,n)
from test_a left join
(select level,rownum n from dual connect by level < 10) on 1=1
where regexp_substr(id,'[^,]+',1,n) is not null
2、如果真想用一个sql完成,那么
SQL> select * from a; ID USERID
---------- --------------------
1 aa,22,bb,cc
2 dd2,3aSQL>
SQL> select id, userid
2 from (select id,
3 substr(',' || userid || ',',
4 instr(',' || userid || ',', ',', 1, rn) + 1,
5 instr(',' || userid || ',', ',', 1, rn + 1) -
6 instr(',' || userid || ',', ',', 1, rn) - 1) as userid
7 from A,
8 (select rownum rn
9 from all_objects
10 where rownum <= (select max(length(userid) -
11 length(replace(userid, ',')) + 1)
12 from A)))
13 where userid is not null; ID USERID
---------- --------------------
1 aa
1 22
1 bb
1 cc
2 dd2
2 3a6 rows selected.