在oracle中将一行字符串拆分成多行
例如,有如下一张表,表名为bk_test。插入了以下数据:
CREATE TABLE BK_TESK(id varchar2(10),s varchar2(20));
insert into BK_TESK values ('A','1,2,3');
insert into BK_TESK values ('B','4,5,6');
insert into BK_TESK values ('C','7,8');
查询数据如图所示
我们需要将字段S中以逗号分隔的数据拆分成多行。结果如图所示
实现将一行数据拆分成多行的代码如下所示
SELECT
id,
TRIM(REGEXP_SUBSTR(s, '[^,]+', 1, LEVEL)) AS s
FROM
BK_TESK
CONNECT BY
PRIOR SYS_GUid() IS NOT NULL
AND LEVEL <= REGEXP_COUNT(s, ',') + 1
AND PRIOR id = id
ORDER BY
id, s;
1: PRIOR id = id这个条件的主要作用是确保层次查询在处理每个id 时保持连续性
- PRIOR 关键字用于引用上一行的值。
- 这个条件使得每次迭代都使用相同的 id 值。
- 它防止查询跨越不同的 id 值,确保每个 id 的所有拆分值都被正确处理。
- 例如,当处理 'a' 的值时,这个条件确保不会跳到 'b' 的值,直到 'a' 的所有值都被处理完。
- id字段必须是唯一值,不然查询结果会出现重复值。如果id不是唯一,可以使用rowid(PRIOR rowid = rowid)