REGEXP_SUBSTR (T.DEPTS, '[^,]+', 1, LEVEL) DEPT VROADWORK_TROADWORKMAIN T CONNECT BY LEVEL <= LENGTH (T.DEPTS) - LENGTH (REPLACE(T.DEPTS, ',', '')) + 1 ORDER BY T.ID;

T.DEPTS是要以逗号分隔的字段

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

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

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,stusex
(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，性能可想而知

SELECT DISTINCT T.ID, REGEXP_SUBSTR (T.DEPTS, '[^,]+', 1, LEVEL) DEPTFROM VROADWORK_TROADWORKMAIN T CONNECT BY LEVEL &amp;lt;= LENGTH (T.DEPTS) - LENGTH (REPLACE(T.DEPTS, ',', '')) + 1ORDER ... REGEXP_SUBSTR函数格式如下： function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier) __srcstr ：需要进行正则处理的字符串 __pattern ：进行匹配的正则表达式 __position ：起始位置，从第几个字符开始正则表达式匹配（默认为 STUSEX T_STU CONNECT BY LEVEL <= LENGTH (STUNAME) - LENGTH (REPLACE(STUNAME, ',', '')) + 1 ORDER BY 利用 ORACLE 的REGEXP_SUBSTR（）进行处理就好了 SELECT DISTINCT REGEXP_SUBSTR( '371,370,368,409,1', '[^,]+', 1, LEVEL ) VERI_ITEM DUAL T CONNECT BY LEVEL <= LENGTH('371,370,368,409,1') - LENGTH( REPLACE ( '371,370,368,409,1', ',', ''