方法一

SELECT DISTINCT STUID,REGEXP_SUBSTR (STUNAME,'[^,]+',1,LEVEL) STUNAME,STUSEX FROM T_STU CONNECT BY LEVEL <= LENGTH (STUNAME) - LENGTH (REPLACE(STUNAME,',','')) + 1 ORDER BY STUID;

方法二

SELECT STUID,lv) STUNAME,STUSEX FROM T_STU,( SELECT LEVEL lv FROM dual CONNECT BY LEVEL < 10 ) b WHERE b.lv <= REGEXP_COUNT (T_STU.STUNAME,'\,') + 1 ORDER BY STUID;

方法三

SELECT stuid,substr(a.stuname,instr(a.stuname,levels.lvl) + 1,levels.lvl + 1) -(instr(a.stuname,levels.lvl) + 1)) as stuname,stusex FROM (SELECT stuid,' || stuname || ',' AS stuname,stusex,length(stuname) - nvl(length(REPLACE(stuname,')),0) + 1 AS cnt --取待拆分字段每行按照分隔符','分割后的记录数，用于connect by FROM T_STU) a,(SELECT rownum AS lvl --产生一个待拆分字段分割后最大记录数的序列 FROM (SELECT MAX(length(stuname || ',') - nvl(length(REPLACE(stuname,0)) max_len FROM T_STU) CONNECT BY LEVEL <= max_len) levels WHERE levels.lvl <= a.cnt --笛卡尔连接 ORDER BY stuid;

性能上方法三最优，其次方法二，方法一因为有distinct，性能可想而知