Oracle 中使用 Regexp_substr 和 Connect By 来根据分隔符进行列转行操作示例

先查询出数据库中记录

select t.ID,t.存储名称,t.存储路径,t.上传人,t.上传时间 from zlmaindata.投标记录 t

以上为查询结果，我要将【存储名称】多文件名按照分号进行分割，然后转换为两行。

使用如下代码查询：

with t1 as( select 存储名称 as c1,存储路径,上传人,上传时间 from zlmaindata.投标记录 where id='')select distinct regexp_substr(t1.存储名称, '[^;]+',1,level) as 文件存储名称,存储路径,t1.上传人,t1.上传时间,regexp_substr(相关附件, '[^;]+',1,level) as 相关附件from zlmaindata.投标记录 connect by level <= length(存储名称) - length(replace(存储名称,';',''));

查询结果如下：

根据结果看达到我的最初目的。

这里说一下这段的作用:

with t1 as( select 存储名称 as c1,存储路径,上传人,上传时间 from zlmaindata.投标记录 where id='')

with as 是先将需要进行操作的数据先查询出来创建一张虚拟表，后面的sql直接从虚拟表中取数据进行函数操作，避免了全表扫描，大大提高了查询转换效率。

以下是另外一个查询示例：

with t as (select q.* from(select rownum as 序号,y.ID as 记录ID,y.附件名,y.登记人,y.登记时间,y.附件路径  from zlmaindata.用户服务单 y where y.附件名 is not nulland y.附件路径 is not null) q where q.序号 between 1 and 20)select distinct t.记录ID,regexp_substr(t.附件名, '[^;]+',1,level) as 文件名,'客户服务' as 模块名称,t.登记人 as 上传人,t.登记时间 as 上传时间,regexp_substr(t.附件名, '[^;]+',1,level) as 存储名,t.附件路径 as 存储路径from t connect by level <= length(t.附件名) - length(replace(t.附件名,';',''))