Oracle 单字段拆分成多行

方法一

1
2
3
4
5
6
7
8
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;

方法二

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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;

方法三

1
2
3
4
5
6
7
8
9
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 --取待拆分字段每行按照分隔符','分割后的记录数,用于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,性能可想而知

查看评论