Oracle数据库单列转多行

在数据库表设计中,经常会出现一对多关系,常用的处理办法有建立中间表和在“一”的那张表中的某个字段引用“多”的那张表,会以某符号分隔存储在表中。
但是在实际运用的过程中可能会用到分隔符引用的列需要转多行的需求,这时候我们一般会有两种处理办法:
- 写函数方法
- 正则表达式

比较而言第二种更加方便快捷,本文详细介绍第二种方式。

regexp_substr函数

REGEXP_SUBSTR函数格式如下:

function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)

__srcstr :需要进行正则处理的字符串

__pattern :进行匹配的正则表达式

__position :起始位置,从第几个字符开始正则表达式匹配(默认为1)

__occurrence :标识第几个匹配组,默认为1

__modifier :模式(’i’不区分大小写进行检索;’c’区分大小写进行检索。默认为’c’。)

举个简单的例子,说明REGEXP_SUBSTR的用法

A:Students(学生)表

idcodenamebooks
11张三1,2,3

B:Book(图书)表

codenameprice
1Java从入门到精通10
2编译原理10
2颈椎康复指南100

现需要统计出所有学生买图书的总价格
select user_id,user_name,

select stu.name, sum(k.price)
    (select name,
      REGEXP_SUBSTR(books, '[^,]+', 1, LEVEL) book
      --regexp_substr(str,reg,起始位置 第几次)
      from students
      CONNECT BY LEVEL <= LENGTH(books) - LENGTH(REGEXP_REPLACE(books, ',')) + 1
      and id = prior id
      and prior dbms_random.value is not null) stu
      --筛选掉空数据
      left join book k on k.code = stu.book

此次sql提取前50行执行时间为1点几秒,且数据量不大,下面为sql优化

select stu.name, sum(k.price)
    (select name,
      regexp_substr(books, '[^,]+', 1, level) book
      --regexp_substr(str,reg,起始位置 第几次)
      from students
      connect by level <= regexp_count(books, ',') + 1
      --regexp_count(teachers, ',') 统计字符串中,的数量
      and id = prior id
      and prior dbms_random.value is not null) stu
      --筛选掉空数据
      left join book k on k.code = stu.book

此次执行仅为0.0几秒,速度快了数十倍

CONNECT BY
  • 它相当于是一个递归的自连接,不断地把每层的连接结果叠加到结果集中。两层之间的连接条件和递归出口写在CONNECT BY中。在这里我们的数据并无父子关系,只是要让同一行数据重复出现,因此我们的连接的条件只用到了表的主键id=PRIOR id, 此外再用LEVEL控制层数作为递归出口。但ORACLE有个检查,如果你有前后连接条件(id=PRIOR id),但是同一行数据再次出现,它就会报一个错:
  • –ERROR:
  • –ORA-01436: CONNECT BY loop in user data
  • –为了欺骗它,这里用了一个PRIOR DBMS_RANDOM.VALUE, 因为DBMS_RANDOM.VALUE每次调用都返回不同结果,所以它认为两行数据不一样,所以不报错了。
  • 3
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值