一行记录拆分为多行
STUID STUNAME STUSEX 1 aa,bb,cc 1 2 dd,ee 0
方法一
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,
REGEXP_SUBSTR (STUNAME, '[^,]+', 1, 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,
',' ,
1 ,
LEVELS. LVL) + 1 ,
INSTR( A. STUNAME,
',' ,
1 ,
LEVELS. LVL + 1 ) -
( INSTR( A. STUNAME,
',' ,
1 ,
LEVELS. LVL) + 1 ) ) AS STUNAME,
STUSEX
FROM ( SELECT STUID,
',' || STUNAME || ',' AS STUNAME,
STUSEX,
LENGTH( STUNAME) - NVL( LENGTH( REPLACE ( STUNAME,
',' ) ) ,
0 ) + 1 AS CNT
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;