Oracle 单字段拆分成多行

性能上方法1最优，其次方法2，方法3因为有distinct，性能差

方法1：

SELECT stuid,substr(a.stuname, instr(a.stuname, ',', 1, levels.lvl) + 1, instr(a.stuname, ',', 1, levels.lvl + 1) -(instr(a.stuname, ',', 1, levels.lvl) + 1)) as stuname,stusexFROM (SELECT stuid,',' || stuname || ',' AS stuname,stusex,length(stuname) - nvl(length(REPLACE(stuname, ',')), 0) + 1 AS cnt --取待拆分字段每行按照分隔符','分割后的记录数，用于connect byFROM 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) levelsWHERE levels.lvl <= t --笛卡尔连接ORDER BY stuid;

方法2：

SELECTSTUID,REGEXP_SUBSTR (STUNAME, '[^,]+', 1, lv) STUNAME,STUSEXFROMT_STU,(SELECTLEVEL lvFROMdual CONNECT BY LEVEL < 10) bWHEREb.lv <= REGEXP_COUNT (T_STU.STUNAME, '\,') + 1ORDER BYSTUID;

方法3：

SELECT DISTINCTSTUID,REGEXP_SUBSTR (STUNAME, '[^,]+', 1, LEVEL) STUNAME,STUSEXFROMT_STU CONNECT BY LEVEL <= LENGTH (STUNAME) - LENGTH (REPLACE(STUNAME, ',', '')) + 1ORDER BYSTUID;